topic Re: Summary in SAS Programming

Summary

SASPhile — Thu, 07 May 2015 15:00:12 GMT

data test;
input id jan feb mar apr;
cards;
1 732 750 748 790
2 760   0   0   0
3 750   0 765 0
4 666   0    0 0
5 777 779 880 0
6 850   0 779 0
;
run;

How to get a summary that tells how many id's have scores present in

All months (id 1)
only first month (id 2 and 4)
two months (id 3 and 6)
consecutive months (id 1 and 5)

Re: Summary

ballardw — Thu, 07 May 2015 15:11:34 GMT

If existence is important then this may be one of those times where you do not want 0 but missing values as NMISS would simplify the logic.

Is your "two months" required to start with Jan or any two months, and does it have to be EXACTLY two months?

Is your consecutive required to start with Jan? Does it count for consecutive if there are only two months?

Are these consecutive or two months?

7 850 779 0 0

8 0 850 779 0

9 0 850 779 110

Re: Summary

RW9 — Thu, 07 May 2015 15:17:14 GMT

IMO having dates of any type as columns is a bad idea. First thing I would do with this data is normalise it:

ID MONTH RESULT

1 Jan 1234

1 Feb 234

...

Its simple then to do sums and such like across the data using SQL or means procedure. If you absolutely have to have them as columns, then name the columns a standard with suffix approach and put other data in the label:

COL1 COL2 ...

"Jan" "Feb"

As then you can use array processing on them. However, with 'data' as columns you may find you have thousands of columns if you have several years data.

Re: Summary

SASPhile — Thu, 07 May 2015 15:17:59 GMT

well.. it deosnt have to start in Jan or feb

Re: Summary

Doc_Duke — Thu, 07 May 2015 15:20:57 GMT

It appears that "0" is not a valid score. So the first step is to change all the "0" values to missings.

Then you can do the first and third with the N function, as

all=(N(of jan--apr)=4);

two=(N(of jan--apr)=2);

The second follows as

first=((jan>.) & (N(of jan-apr)=1)));

The last one will require some if-the-else logic or a Perl expression. My Perl is too rusty to provide useful thoughts.

Re: Summary

SASPhile — Thu, 07 May 2015 16:20:47 GMT

can the values be defined under one variable?

Re: Summary

Reeza — Thu, 07 May 2015 16:52:59 GMT

Post an example of how you'd want that to look please

Re: Summary

slchen — Thu, 07 May 2015 17:20:11 GMT

data test;

input id jan feb mar apr;

array month Jan--Apr;

length flag $15.;

if nmiss(of month(*))=0 then flag='All';

else if nmiss(of month(*))=3 then flag='First';

else if nmiss(of month(*))=1 then do;

if whichc(.,of month(*)) in (1,4) then flag='Consecutive';

else flag='Two Months';

end;

else if nmiss(of month(*))=2 then flag='Two Months';

cards;

1 732 750 748 790

2 760 . . .

3 750 . 765 .

4 666 . . .

5 777 779 880 .

6 850 . 779 .

;

run;

Re: Summary

bharathtuppad — Fri, 08 May 2015 08:44:05 GMT

data test;
input id jan feb mar apr;
cards;
1 732 750 748 790
2 760   0   0   0
3 750   0 765 0
4 666   0    0 0
5 777 779 880 0
6 850   0 779 0
;
run;

proc transposrt data=test out=new;
by id;
run;
proc print data=new;
run;

proc sql;
select count(*) from new
where col1>0
group by id;
quit;