DATA have;
input id yq
;
DATALINES;
1001 23
1001 24
1001 25
1001 26
1001 27
1001 28
1005 102
1005 103
1005 104
1005 105
1005 106
1005 107
1005 108
1005 109
1005 110
1005 111
1005 112
1005 113
1005 114
2001 43
2001 44
2001 45
2001 46
2001 47
2001 48
;
RUN;
I have the above dataset, now I have another dataset that contains a list like this (there is never a repeat in this dataset):
DATA list;
input event_yq
;
DATALINES;
57
106
139
;
RUN;
I want to create the following dataset where I want to fill in the columns event_yq1 event_yq2 event_yq3 (until however many there values there are in event_yq):
DATA want;
input id yq event_yq1 event_yq2 event_yq3
;
DATALINES;
1001 23 57 106 139
1001 24 57 106 139
1001 25 57 106 139
1001 26 57 106 139
1001 27 57 106 139
1001 28 57 106 139
1005 102 57 106 139
1005 103 57 106 139
1005 104 57 106 139
1005 105 57 106 139
1005 106 57 106 139
1005 107 57 106 139
1005 108 57 106 139
1005 109 57 106 139
1005 110 57 106 139
1005 111 57 106 139
1005 112 57 106 139
1005 113 57 106 139
1005 114 57 106 139
2001 43 57 106 139
2001 44 57 106 139
2001 45 57 106 139
2001 46 57 106 139
2001 47 57 106 139
2001 48 57 106 139
;
RUN;
I have tried transposing the list dataset and then using a merge statement but I can only get one row filled out and the rest are missing. Any help would be appreciated.
What you probably want is to use the "if _N_=1 then set" construction:
proc transpose data=list prefix=event_yq out=transposed(keep=event_yq:);
var event_yq;
run;
data want;
set have;
if _N_=1 the set transposed;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.