Comma-delimited field transpose
I have a dataset that looks like this and I am using SAS Enterprise Guide 6.3:
data have;
input id state $;
cards;
134 NC,NY,SC
145 AL,NC,NY,SC
;
run;
I have another dataset that has multiple metrics for each id in each state, but I only need to pull data for the states listed in the second column of the dataset.
data complete;
input id state $ metric;
cards;
134 AL 5
134 NC 4.3
134 NY 4
134 SC 5.5
145 AL 1.3
145 NC 1.3
145 NY 1.5
145 SC 1.1
177 AL 10
177 NC 74
177 NY 23
177 SC 33
;
run;
I was thinking about using trnwrd to replace the comma ',' and concatenate the start and end quote to make the list a character list so that I can use the WHERE IN statement. However, I think it would be more helpful if I could somehow rearrange the comma-separated list to something like this:
data have_mod;
input id state $;
cards;
134 NC
134 NY
134 SC
145 AL
145 NC
145 NY
145 SC
;
run;
Then I could just join this table to the full data table to get the subset I want (below).
data want;
input id state $ metric;
cards;
134 NC 4.3
134 NY 4
134 SC 5.5
145 AL 1.3
145 NC 1.3
145 NY 1.5
145 SC 1.1
;
run;
Any thoughts? Thank.
source to share
I would do exactly what you suggest and transfer it, except I would read it that way.
data have;
infile datalines truncover dlm=', ';
length state $2;
input id @; *read in the id for that line;
do until (state=''); *keep reading in until state is missing = EOL;
input state $ @;
if not missing(state) then output;
end;
cards;
134 NC,NY,SC
145 AL,NC,NY,SC
;
run;
Alternatively, you can SCAN
for the first status code.
data want_to_merge;
set have;
state_first = scan(state,1,',');
run;
SCAN
is a function equivalent to reading a delimited file.
source to share