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;
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
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,
Thank you.
What is the target of the code when you create array?
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
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 ?
@ 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
It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.
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.
Ready to level-up your skills? Choose your own adventure.