## SAS Base Query

Solved
Frequent Contributor
Posts: 78

# SAS Base Query

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;

Accepted Solutions
Solution
‎03-15-2017 06:09 AM
Super Contributor
Posts: 268

## Re: SAS Base Query

[ Edited ]

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;
``````

All Replies
Super User
Posts: 6,765

## Re: SAS Base Query

[ Edited ]

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.

Solution
‎03-15-2017 06:09 AM
Super Contributor
Posts: 268

## Re: SAS Base Query

[ Edited ]

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;
``````
Super User
Posts: 10,238

## Re: SAS Base Query

@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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super Contributor
Posts: 268

## Re: SAS Base Query

[ Edited ]

Following on from @KurtBremser, 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.

☑ This topic is solved.