topic Re: melt dataset in SAS Programming

melt dataset

France — Tue, 18 Sep 2018 13:33:45 GMT

Hello all,

how could I create a table from

psn_name, application200001, application200002, application200003
a,1,2,3
b,2,3,4

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.

Re: melt dataset

ballardw — Tue, 18 Sep 2018 14:07:44 GMT

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.

Re: melt dataset

Ksharp — Tue, 18 Sep 2018 14:14:42 GMT

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;

Re: melt dataset

Reeza — Tue, 18 Sep 2018 15:08:25 GMT

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/

Re: melt dataset

France — Tue, 18 Sep 2018 16:14:40 GMT

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