proc transpose from multiple rows to one row per customer

[Ronein]

Hello

I have in the "have" data set  information of failure code for each customer in a specific date.(In this example the information is for 19 APRIL 20202 so it is written as 19042020 like DDMMYY format). 

Please note that a specific customer can appear in multiple rows if he had multiple failures codes.

I want to change the structure of data  to one row per customer .(Please see "Wanted1 data set).

Then I want to order to columns by the code (Please see "Wanted2" data set)

My question is what is the code to create "Wanted1" and "Wanted2" data sets from "have " data set.

Data Have;
input  ID  Info_19042020;
cards;
999 34
999 11
888 34
777 28
;
Run;

Data Wanted1;
input  ID   info_19032020_C34  info_19032020_C11  info_19032020_C28;
cards;
999 1 1 0
888 1 0 0
777 0 0 1
;
run;


Data Wanted2;
input ID    info_19032020_C11   info_19032020_C28   info_19032020_C34;
cards;
999 1 0 1
888 0 0 1
777 0 1 0
;
run;

[ed_sas_member]

Hi @Ronein 

You can do this for example:

%let date = %sysfunc(compress(%sysfunc(intnx(month,%sysfunc(inputn(19042020,DDMMYY10.)),-1,s),DDMMYY10.),/));

Data Have;
input  ID  Info_19042020;
cards;
999 34
999 11
888 34
777 28
;
Run;

proc transpose data=have out=_wanted1 (drop=_:) prefix=info_&date._C;
	var Info_19042020;
	id Info_19042020;
	by descending ID;
run;

data wanted1;
	set _wanted1;
	array _a (*) info:;
	do i=1 to dim(_a);
		if _a(i) = . then _a(i) = 0;
		else _a(i) = 1;
	end;
	drop i;
run;

proc sql noprint;
	select name into: varname separated by ","
	from dictionary.columns
	where libname="WORK" and memname="WANTED1" and find(lowcase(name),"info_")>0
	order by name;
run;

proc sql;
	create table wanted2 as
	select ID, &varname.
	from wanted1;
run;

Best,

[Ronein]

Thank you.

What is the target of the code when you create array?

[yabwon · Accepted solution]

Like that:

Data Have;
input  ID  Info_19042020;
cards;
999 34
999 11
888 34
777 28
;
Run;

%let variable_name = Info_19042020;

proc sort data = Have;
  by descending ID &variable_name.;
run;

proc transpose data = Have out = Wanted1(drop = _NAME_) prefix=&variable_name._;
  by descending ID;
  id &variable_name.;
  var &variable_name.;
run;
proc print data = Wanted1;
run;

proc transpose 
  data=Wanted1(obs=0) 
  out=vars(keep = _name_ where = (_name_ like "&variable_name."!!'%' ));
  var _ALL_;
run;

proc sort data = vars sortseq=linguistic(NUMERIC_COLLATION = on);
  by _name_;
run;

proc sql noprint;
  select _name_ into :vars separated by " " from vars;
quit;


Data Wanted2;
  retain ID &vars.;
  set Wanted1;
  array V &vars.;
  do over V;
    V = V&1;
  end;
run;
proc print data = Wanted2;
run;

all the best

Bart

[Ronein]

Great great great!

Can you please also explain:

1-what is the target of using numeric_collation in proc sort

2-what is the target of using array in last step? I understand the using of Retain in order to sort the columns by the order that we created using a macro parameter but why need also array ?

[yabwon]

@ 1) 

data x;
  x = '11'; output;
  x = '12'; output;
  x = '2'; output;
  x = '1'; output;
run;
proc print;
run;
proc sort data = x out = x1 sortseq=linguistic(NUMERIC_COLLATION = off);
  by x;
run;
proc print;
run;
proc sort data = x out = x2 sortseq=linguistic(NUMERIC_COLLATION = on);
  by x;
run;
proc print;
run;

@ 2)

you wanted to have 1 and 0, without the `V & 1` the values are "Code of error" or null.

All the best

Bart