topic Proc SQL and similar SAS Code in SAS Procedures

Proc SQL and similar SAS Code

AshleyM — Wed, 28 Mar 2012 18:43:36 GMT

I'm trying to write the equivalent SAS code for:

proc sql;

create table dataset2 as select var1, var2, var3, var4, var5, count(*)

from dataset1

group by var1, var2, var3, var4, var5;

Proc SQL and similar SAS Code

FriedEgg — Wed, 28 Mar 2012 18:51:26 GMT

proc sort data=dataset1; by var1 var2 var3 var4 var5; run;

data dataset2;

set dataset1;

by var1 var2 var3 var4 var5;

if first.var5 then count=0;

count+1;

if last.var5 then output;

run;

Proc SQL and similar SAS Code

sassharp — Wed, 28 Mar 2012 18:58:28 GMT

data dataset1;
input var1 var2 var3 var4 var5;
datalines;
1 3 65 63 77
1 2 65 63 66

;
proc sort data=dataset1;
by var1 var2 var3 var4 var5;
run;

data dataset2(keep= var1 var2 var3 var4 var5 count);
set dataset1;
by var1 var2 var3 var4 var5;
count+1;
run;

Proc SQL and similar SAS Code

FriedEgg — Wed, 28 Mar 2012 19:01:07 GMT

sassharp,

Your code will count across the entire file rather than across by group as the SQL will. You are basically creating a sequence number for dataset1.

Proc SQL and similar SAS Code

sassharp — Wed, 28 Mar 2012 19:05:09 GMT

Agreed Fried Egg.

Proc SQL and similar SAS Code

sassharp — Wed, 28 Mar 2012 19:07:18 GMT

As said friedEgg

data dataset1;
input var1 var2 var3 var4 var5;
datalines;
1 3 65 63 77
1 2 65 63 66
2 2 2 2 2
2 2 2 2 2

;
proc sort data=dataset1;
by var1 var2 var3 var4 var5;
run;

data dataset2(keep= var1 var2 var3 var4 var5 count);
set dataset1;
by var1 var2 var3 var4 var5;
if first.var5 then count=0;
count+1;
if last.var5 then output;

run;

Proc SQL and similar SAS Code

sassharp — Wed, 28 Mar 2012 19:14:40 GMT

data dataset1;
input var1 var2 var3 var4 var5;
datalines;
1 3 65 63 77
1 2 65 63 66
2 2 2 2 2
2 2 2 2 2

;
proc sort data=dataset1;
by var1 var2 var3 var4 var5;
run;

data dataset2(keep= var1 var2 var3 var4 var5 count);
set dataset1;
by var1 var2 var3 var4 var5;
if first.var5 then count=0;
count+1;
if last.var5 then output;

run;

/*or*/

proc sql;
create table dataset2 as
select var1,var2,var3,var4,var5,count(*)
from dataset1
group by var1,var2,var3,var4,var5;
run;

Proc SQL and similar SAS Code

Astounding — Wed, 28 Mar 2012 19:23:28 GMT

Another approach:

proc freq data=dataset1;

tables var1 * var2 * var3 * var4 * var5 / noprint out=dataset2 (drop=percent);

run;

If there are a lot of combinations of the 5 variables, it is conceivable this would run out of memory. But the tradeoff is that it works on unsorted data.

Proc SQL and similar SAS Code

FriedEgg — Wed, 28 Mar 2012 19:35:56 GMT

Another alternative would be proc means, which is a multi-threading procedure which will make the operation faster for larger data:

proc means data=dataset1 n nway noprint;

class var1-var5;

output out=dataset2(drop=_type_ rename=(_freq_=count));

run;

Re: Proc SQL and similar SAS Code

Haikuo — Thu, 29 Mar 2012 13:58:10 GMT

Another approach, using Hash this time, I know it's silly, just try to show what Hash can do:

data have;

input v1-v6;

datalines;

2 2 2 2 2 4

2 2 2 2 2 77

1 3 65 63 77 2

1 2 65 63 66 3

2 2 2 2 2 76

;

data want (drop=_rc);

length sum 8;

if _n_ = 1 then do;

declare hash h(suminc: "sum", ordered: "a");

declare hiter hi("h");

h.defineKey('v1', 'v2','v3', 'v4', 'v5');

h.definedata('v1', 'v2','v3', 'v4', 'v5');

h.defineDone();

sum = 1;

end;

do while (not done);

set have end=done;

_rc = h.ref();

end;

_rc = hi.first();

do while(_rc = 0);

_rc = h.sum(sum: sum);

output;

_rc = hi.next();

end;

stop;

run;

proc print;run;

Regards,

Haikuo

Proc SQL and similar SAS Code

Haikuo — Thu, 29 Mar 2012 14:25:39 GMT

DOW seems pretty handy for this case:

data dataset1;

input var1 var2 var3 var4 var5;

datalines;

1 3 65 63 77

1 2 65 63 66

2 2 2 2 2

;

proc sort data=dataset1;

by var1 var2 var3 var4 var5;

run;

data want;

do sum=1 by 1 until (last.var5);

set dataset1;

by var1-var5;

end;

run;

proc print;run;

Regards,

Haikuo