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
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;
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;
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
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;
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.;
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
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
Hi.
Your task is not clear to me. May be my English is not good enough.
I hope someone else can help you.
Oleg.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: