BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ronein
Onyx | Level 15

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;


1 ACCEPTED SOLUTION

Accepted Solutions
yabwon
Amethyst | Level 16

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



View solution in original post

5 REPLIES 5
ed_sas_member
Meteorite | Level 14

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
Onyx | Level 15

Thank you.

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

 

yabwon
Amethyst | Level 16

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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



Ronein
Onyx | Level 15

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
Amethyst | Level 16

@ 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

_______________
Polish SAS Users Group: www.polsug.com and communities.sas.com/polsug

"SAS Packages: the way to share" at SGF2020 Proceedings (the latest version), GitHub Repository, and YouTube Video.
Hands-on-Workshop: "Share your code with SAS Packages"
"My First SAS Package: A How-To" at SGF2021 Proceedings

SAS Ballot Ideas: one: SPF in SAS, two, and three
SAS Documentation



hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 5 replies
  • 2013 views
  • 2 likes
  • 3 in conversation