SAS Tips

Some very good tips in this presentation

http://www.wuss.org/proceedings09/09WUSSProceedings/papers/tut/TUT-Lafler2.pdf

SAS programming manua

http://support.sas.com/documentation/cdl/en/basess/58133/PDF/default/basess.pdf

SAS® Programming Guidelines
http://www2.sas.com/proceedings/sugi31/123-31.pdf

Proc SQL And UNION

There are five Union operators in Proc SQL. Four perform very different operations than the simple concatenation seen here – and several of those are very difficult to code in the SAS Data Step.

Union - matches by column position, not name, and drops duplicate rows

Union All - matches by column position, not name, and doesn’t drop duplicate rows

Union Corresponding - matches by column name and drops duplicate rows

Except - matches by column name and drops the rows found in both tables

Intersection - matches by column name and keeps the unique rows in both tables

The Unions can be combined also. Two tables could be concatenated with a Union Corresponding and then that result intersected with another table to find the records in the last table that were also in either the first table or the second.

Copy/Move Files using Data Step

I found this post on "The SAS Dummy" Blog by Chris Hemedinger. It is an example of how to copy and move files anywhere. Thank You Chris. I have found your blog very informative and helpful.


/* these IN and OUT filerefs can point to anything */
filename in "c:\dataIn\input.xlsx";
filename out "c:\dataOut\output.xlsx";

/* copy the file byte-for-byte */
data _null_;
length filein 8 fileid 8;
filein = fopen('in','I',1,'B');
fileid = fopen('out','O',1,'B');
rec = '20'x;
do while(fread(filein)=0);
rc = fget(filein,rec,1);
rc = fput(fileid, rec);
rc =fwrite(fileid);
end;
rc = fclose(filein);
rc = fclose(fileid);
run;

Proc SQL - Unmatched Records

1) How to keep only unmatched records from 2 tables;
data a; input varA @@; cards; 200 300 400 500 ; run;
data b; input varB @@; cards;600 700 200 300 ; run;

2) there are 2 tables one is master table with 10 million rows and second has 10000 rows

Need to update the master table records with the records from 2nd table.

Solution
Proc SQL

proc sql;
select VAR, src
from
(Select distinct a.VAR, 'a' as src from work.a a
outer union corr
Select distinct b.VAR, 'b' as src from work.b b
)
group by VAR
having count(VAR) eq 1
;
quit;

Another Frequently used Excel Formatting trick

Hello all,

Whenever you export a numeric colum with leading zeros, excel trims/removes the leading zeros. I was running into an issue where I needed the leading zeros to be as is since they were meaningful(like in the case of zip codes which start with a zero) After a lot of searching I found the article

How to add leading zeros in Excel on eHow.com

which explains process in very simple terms. Here is the link to the article

http://www.ehow.com/how_4703826_add-leading-zeros-excel.html


Happy Excelling

My journey to PMP Land

Hello All,
One beautiful day, I had an epiphany that I should get my PMP certification. I have been working on projects all the time, I follow the PMP methodology, the one thing I did not have was the certification. So now I am in preparing to get certified. I am going to document my process and I hope this will be helpful for others who intend to get certified.


Here is what I have done so far.
I completed the application form on PMI.org and became a member of PMI.
I have downloaded the PMBOK 4th edition and read it once.
I have purchased Andy Crowe's PMP certification Book and am in the process of reading the book.

Now I have to prepare a plan to study and pass the exam.

Shri

Excel Tips and tricks

Since I took up my position as a Data analyst, I seem to be using more and more of Excel with SAS. I'll write about SAS in another post. But coming back to Excel, I seem to be discovering new things every time I use it, some are quirky some are very useful. Like this one time, I was trying to use Vlookup and for some reason (unknown to me at that time) I was getting #N/A even though the search string was present in the data that I was searching, after some hairpulling and searching, I found that lookup data has to be sorted on the lookup column and the lookup column and the lookup data need to be the same data type.