SAS Base Query

[scb]

May I know how to get the output from the following input? Thanks.

ID      ABC BBC

123   1      1

124   1      2

125   0      3

DATA A;

INFILE CARDS;

INPUT ID $ ACCT $;

CARDS;

123 ABC

123 BBC

124 ABC

124 BBC

124 BBC

125 BBC

125 BBC

125 BBC

;

RUN;

[Astounding]

If you iknow that ACCT will always be "ABC" or "BBC" then the programming is pretty straightforward.  If necessary to get the data in order, sort first:

proc sort data=have;

by id;

run;

Then calculate:

data want;

set have;

by id;

if first.id then do;

   ABC=0;

   BBC=0;

end;

select (ACCT);

   when ("ABC") ABC + 1;

   when ("BBC") BBC + 1;

end;

if last.id;

run;

The way the SELECT statement works, it will automatically give you an error if ACCT is neither "ABC" nor "BBC".  That can be a blessing or a curse, and can be changed if necessary.

If you don't know the values of ACCT ahead of time, the result can still be achieved but requires entirely different programming.

[LaurieF · Accepted solution]

How's this? I've always been a big fan of proc freq:

DATA A;
INFILE CARDS;
INPUT ID $ ACCT $;
CARDS;
123 ABC
123 BBC
124 ABC
124 BBC
124 BBC
125 BBC
125 BBC
125 BBC
;
RUN;

proc freq data=a noprint;
table id * acct / out=want nopercent;
run;

option missing = 0; /* Replaces missing values with - well - zeros */
proc transpose data=want out=want(drop=_name_ _label_);
by id;
id acct;
var count;
run;

[Kurt_Bremser]

@LaurieF beat me to it with the exact identical solution.

I just want to add that

options missing=0;

only controls the display of missing values, the values themselves will stay missing.

[LaurieF]

Following on from @Kurt_Bremser, if you must have 0 and not missing, just tack this on the end:

data want;
set want;
array counts[*] _numeric_;
do i = 1 to dim(counts);
   counts[i] = coalesce(counts[i], 0);
   end;
drop i;
run;

 Oh, and if your source dataset is huge, the freq may have problems with memory. Ameliorate that with this (assuming that it's already sorted):

proc freq data=a noprint;
by id;
table acct / out=want nopercent;
run;

The transpose will still do an initial pass through the freq output looking for the distinct values of acct so that it knows how much to rotate, but it's still pretty decent on memory handling.