melt dataset

[France]

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.

[ballardw · Accepted solution]

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.

[Ksharp]

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;

[Reeza]

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/

[France]

Hello Reeza, I appreciate your advice paper which helps me a lot.