I agree with @PaigeMiller about the discussion wide versus long.
But if you want to go this way, here is a sample that gets you close. Please note, SAS does not allow for variable names to start with a digit by default. You have to set SAS System Option VALIDVARNAME=ANY. This then also means you have to use a name literal for those new names like so '201909'n.
data dataset;
input id $1-3 yrmth 5-10 count 12;
datalines;
001 201911 2
001 201912 1
001 201903 1
001 202004 5
001 202006 1
002 201908 1
002 201909 2
002 201909 1
002 201910 1
003 202001 4
003 202002 3
003 202005 2
003 202007 1
;
/*
* create orderd lit of column names
*/
proc sql noprint;
select distinct
cats("'", yrmth, "'n")
into
:yrmth_list separated by " "
from
dataset
order by
1
;
quit;
%put NOTE: &=yrmth_list;
/*
* create an empty base table
* using the list from above
*/
data basetable;
length
id $ 3
&yrmth_list 8
;
stop;
run;
/*
* aggregate data, since some yrmth appear more tan once
*/
proc sql;
create table dataset_aggr as
select
id
, yrmth
, sum(count) as count
from
dataset
group by
id
, yrmth
;
quit;
/*
* transpose the data
*/
proc transpose
data=dataset_aggr
out=ds_trsp(drop=_name_)
;
by id;
id yrmth;
var count;
run;
/*
* append t base table
*/
proc append
base=basetable
data=ds_trsp
;
run;
... View more
