BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
scb
Obsidian | Level 7 scb
Obsidian | Level 7

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;

1 ACCEPTED SOLUTION

Accepted Solutions
LaurieF
Barite | Level 11

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;

View solution in original post

4 REPLIES 4
Astounding
PROC Star

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
Barite | Level 11

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
Super User

@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
Barite | Level 11

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 916 views
  • 3 likes
  • 4 in conversation