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/
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: