Hello all,
how could I create a table from
psn_name, application200001, application200002, application200003
a,1,2,3
b,2,3,4
to
psn_name, date, Number_of_applications
a,200001,1
a,200002,2
a,200003,3
b,200001,2
b,200002,3
b,200003,4
an example I need is
could you please give me some suggestions?
thanks in advance.
This creates an actual date value that represents the first day of the month. Use any date format to display the date that you would like.
data have; infile datalines dlm=','; input psn_name $ application200001 application200002 application200003 ; datalines; a,1,2,3 b,2,3,4 ; run; proc transpose data=have out=havetrans; by psn_name; var application:; run; data want; set havetrans; date= input(substr(_name_,12,6),yymmn6.); format date yymmn.; drop _name_; rename col1=Number_of_applications; run;
If the data is not sorted by psn_name it will need to be sorted before the Proc Transpose step.
This creates an actual date value that represents the first day of the month. Use any date format to display the date that you would like.
data have; infile datalines dlm=','; input psn_name $ application200001 application200002 application200003 ; datalines; a,1,2,3 b,2,3,4 ; run; proc transpose data=have out=havetrans; by psn_name; var application:; run; data want; set havetrans; date= input(substr(_name_,12,6),yymmn6.); format date yymmn.; drop _name_; rename col1=Number_of_applications; run;
If the data is not sorted by psn_name it will need to be sorted before the Proc Transpose step.
data have;
infile cards dlm=',';
input psn_name $ application200001 application200002 application200003;
cards;
a,1,2,3
b,2,3,4
;
run;
data want;
set have;
array x{*} application:;
do i=1 to dim(x);
date=compress(vname(x{i}),,'kd');
number=x{i};
output;
end;
keep psn_name date number;
run;
Melt is the R function, transpose is the actual action of flipping a table and the generic term used in most languages.
PROC TRANSPOSE transposes both wide to long and long to wide.
Or an array method can be used.
PROC TRANSPOSE
https://stats.idre.ucla.edu/sas/modules/how-to-reshape-data-wide-to-long-using-proc-transpose/
Array
https://stats.idre.ucla.edu/sas/modules/reshaping-data-wide-to-long-using-a-data-step/
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.