DATA Step, Macro, Functions and more

table editing with (probably) SQL, CALL EXECUTE, List of Values in a macro variable (SYMPUT SYMGET)

Accepted Solution Solved
Reply
Contributor
Posts: 57
Accepted Solution

table editing with (probably) SQL, CALL EXECUTE, List of Values in a macro variable (SYMPUT SYMGET)

Hello

i have a table like this

NameFirstnameNumberHobby1Hobby2Hobby3Adress1Adress2Adress3Tel1Tel2Tel3Mobil1Mobil2Mobil3X1X2X3
LeeMike1readingP301234012345open
Frank

Sue

2bikingswimmingB23P1223453456023456n.a.

closed

n.a.
WoodEdward3sleepingreadingtravellingB12P30B29456767898901034567045678n.a.closedn.a.open
MuellerAnna3theatercinemabikingP35P25B248765456263510172630354542036352openopenclosed

and i want to sort the data into the following form:

NameFirstnameNumberHobbyAdressTelMobil

X

LeeMike1readingP301234012345

open

FrankSue2bikingB232345023456closed
FrankSue2swimmingP123456n.a.

n.a.

WoodEdward3sleepingB124567034567

closed

WoodEdward3readingP306789045678

n.a.

WoodEdward3travellingB298901n.a.open

so originally some variables (in my real table many of them) are numbered with 1,2,3. I want to copy the entrys in the rows, which are numbered with 2 or 3, into a new row right after the row numbered with 1.

I hope this example is understandable. I was thinking about using something like CALL EXECUTE, PROC SQL INSERT. but i dont know how i can make SAS understand, which values are to be inserted into the new row. and since originally i have many of these variables, i dont know if i need to save the variables into a macro variable or how i should do this. :smileyconfused:

I will be very thankful if someone can give me some tips, how i could solve this problem. and since i am pretty new in SAS, a bit more detailed code would be really helpful!

BR  Dingdang


Accepted Solutions
Solution
‎10-01-2013 06:27 AM
Regular Contributor
Posts: 151

Re: table editing with (probably) SQL, CALL EXECUTE, List of Values in a macro variable (SYMPUT SYMGET)

var _all_;

run;

data w2; set w1; length name1 $10.;

if substr(_name_,length(_name_),1) in ('1' '2' '3') then do;

name1=substr(_name_,1,length(_name_)-1);

id=input(substr(_name_,length(_name_),1),8.);

end;

else name1=_name_;

run;

filename c temp;

data _null_; length dd $300. kp $300.; retain dd kp;

file c;

set w2 end=eof;; by name1 notsorted ;

if _n_=1 then put 'data want; set have;';

if first.name1 then kp=catx(' ',kp,name1);

if first.name1 and not missing(id) then do;

put 'array _' name1 name1 +(-1)':;';

dd=catt(dd,';',name1,'=_',name1,'(i)');

end;

if eof then do;

put 'do i=1 to 3;';

put dd; put ';output;end;';

put 'keep' +(1) kp ';run;';

end;

run;

%include c /source2;

View solution in original post


All Replies
Regular Contributor
Posts: 151

Re: table editing with (probably) SQL, CALL EXECUTE, List of Values in a macro variable (SYMPUT SYMGET)

infile cards dlm=',' dsd truncover;

informat Name $10. Firstname $10. Number 8. Hobby1 $10. Hobby2 $10. Hobby3 $10. Adress1 $10. Adress2 $10. Adress3 $10. Tel1 $10. Tel2 $10. Tel3 $10.

              Mobil1 $10. Mobil2 $10. Mobil3 $10. X1 $10. X2 $10. X3 $10.;

input Name Firstname Number Hobby1 Hobby2 Hobby3 Adress1 Adress2 Adress3 Tel1 Tel2 Tel3 Mobil1 Mobil2 Mobil3 X1 X2 X3;

cards;

Lee,Mike,1,reading,,,P30,,,1234,,,012345,,,open,,,

Frank,Sue,2,biking,swimming,,B23,P12,,2345,3456,,023456,n.a,,closed,n.a.,,,

Wood,Edward,3,sleeping,reading,travelling,B12,P30,B29,4567,6789,8901,034567,045678,n.a.,closed,n.a.,open,

Mueller,Anna,3,theater,cinema,biking,P35,P25,B24,8765,4562,6351,017263,0354542,036352,open,open,closed,

;

data want;

set have;

array _hobby hobby:; array _adress adress:; array _tel tel:; array _mobil mobil:; array _x x:;

do i=1 to 3; hobby=_hobby(i); adress=_adress(i); tel=_tel(i); mobil=_mobil(i); x=_x(i); output;end;

keep name firstname number hobby adress tel mobil x;

run;

Contributor
Posts: 57

Re: table editing with (probably) SQL, CALL EXECUTE, List of Values in a macro variable (SYMPUT SYMGET)

Hi Oleg,

thanks for your fast answer. this does almost exactly what i want! Smiley Wink The only problem is, i have many of those variables, about 200 of them, not only hobby, adress, tel,..but many more. Is there any way that i can avoid tiping in the variable names one by one with keep? and the same to array definition.

many many thanks again!

BR  Dingdang

Solution
‎10-01-2013 06:27 AM
Regular Contributor
Posts: 151

Re: table editing with (probably) SQL, CALL EXECUTE, List of Values in a macro variable (SYMPUT SYMGET)

var _all_;

run;

data w2; set w1; length name1 $10.;

if substr(_name_,length(_name_),1) in ('1' '2' '3') then do;

name1=substr(_name_,1,length(_name_)-1);

id=input(substr(_name_,length(_name_),1),8.);

end;

else name1=_name_;

run;

filename c temp;

data _null_; length dd $300. kp $300.; retain dd kp;

file c;

set w2 end=eof;; by name1 notsorted ;

if _n_=1 then put 'data want; set have;';

if first.name1 then kp=catx(' ',kp,name1);

if first.name1 and not missing(id) then do;

put 'array _' name1 name1 +(-1)':;';

dd=catt(dd,';',name1,'=_',name1,'(i)');

end;

if eof then do;

put 'do i=1 to 3;';

put dd; put ';output;end;';

put 'keep' +(1) kp ';run;';

end;

run;

%include c /source2;

Regular Contributor
Posts: 151

Re: table editing with (probably) SQL, CALL EXECUTE, List of Values in a macro variable (SYMPUT SYMGET)

You may want to increase the length of variables dd and kp if you have many variables.

See line:

data _null_; length dd $300. kp $300.;

Contributor
Posts: 57

Re: table editing with (probably) SQL, CALL EXECUTE, List of Values in a macro variable (SYMPUT SYMGET)

cool, this works definitely with the example data. I am going to try that on my real data and see how it goes Smiley Happy Thanks again for the great help!

BR  Dingdang

Contributor
Posts: 57

Re: table editing with (probably) SQL, CALL EXECUTE, List of Values in a macro variable (SYMPUT SYMGET)

Hi Oleg,

I have a similar question again. In my table I have groups of variables like you helped me with before. So it looks like everyone should fill three lists, one to the first group, one to the second and one to the third. In ordner to reduce the workload for them, i am putting in another two variables. On the top of the second list, it would be asked, if the values they fill in for the second group should be the same as the first one. If they choose yes, then they can just leave the second one unfilled and jump to the third list, and will be asked the same question again, if the values should be the same to the first one or to the second one. If they choose, it should be the same as the second one, then they leave the list unfilled again and finish. If they choose no, then they fill the third list per hand. But overall, this way should be able to reduce the work for some persons.

BUT the work is left for me. I have then always the first group of variables filled, and answers to two questions. in the worst case, the second and third lists are then empty, and the values i finally got into SAS are missing. How i can copy the values of the first group (or sometimes the second group) into the second group or the third group? I am thinking about using arrays, but not sure how to do it.

A possible table would look like this:

input Name Firstname Number Hobby1 Adress1 X1 Y1 Z1 SameAsPrior2 Hobby2 Adress2 X2 Y2 Z2 SameAsPrior3 Hobby3 Adress3 X3 Y3 Z3;

cards;

Lee, Mike, reading, P30, open, closed, open, 1,,,,,,1,,,,,,

Frank, Sue, biking, B20, closed, closed, closed, 2, reading, B12, open, closed, closed, 2,,,,,,

Mueller Anna, theater, P13, open, open, closed, 2, reading, P21, closed, open, open, 1,,,,,,

;

the variable SameAsPrior2 has two values, 1 means yes, same to the first group, 2 means not same to the first group.

the varibale SameAsPrior3 has three values, 1 means same to group 1, 2 means same to group 2 and 3 means not same to the first two groups.

I hope I made the problem clear. Hope you can help me with this problem again. thanks so much!!!

BR  Dingdang

Regular Contributor
Posts: 151

Re: table editing with (probably) SQL, CALL EXECUTE, List of Values in a macro variable (SYMPUT SYMGET)

Hi.

Your task is not clear to me. May be my English is not good enough.

I hope someone else can help you.

Oleg.

Contributor
Posts: 57

Re: table editing with (probably) SQL, CALL EXECUTE, List of Values in a macro variable (SYMPUT SYMGET)

Hi Oleg,

thanks for your answer. sorry i didnt formulate my question that clearly. I tried to describe my task in another discussion: table editing with (probably) array, call execute and macro.

I would be very thankful if you could take another look at the task.

BR  Dingdang

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 422 views
  • 3 likes
  • 2 in conversation