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

[Dingdang]

Hello

i have a table like this

Name	Firstname	Number	Hobby1	Hobby2	Hobby3	Adress1	Adress2	Adress3	Tel1	Tel2	Tel3	Mobil1	Mobil2	Mobil3	X1	X2	X3
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

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

Name	Firstname	Number	Hobby	Adress	Tel	Mobil	X
Lee	Mike	1	reading	P30	1234	012345	open
Frank	Sue	2	biking	B23	2345	023456	closed
Frank	Sue	2	swimming	P12	3456	n.a.	n.a.
Wood	Edward	3	sleeping	B12	4567	034567	closed
Wood	Edward	3	reading	P30	6789	045678	n.a.
Wood	Edward	3	travelling	B29	8901	n.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

[Oleg_L]

data have;

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;

[Dingdang]

Hi Oleg,

thanks for your fast answer. this does almost exactly what i want! 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

[Oleg_L · Accepted solution]

If you have three variables in each group. Hope this helps.

proc transpose data=have (obs=0) out=w1;

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;

[Oleg_L]

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.;

[Dingdang]

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

BR  Dingdang

[Dingdang]

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

[Oleg_L]

Hi.

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

I hope someone else can help you.

Oleg.

[Dingdang]

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