Dear All,
I got a dataset as per below:-
group_no | item_name | company |
020101 | banana | |
020101 | apple | |
020101 | grape | |
Company A | ||
020101 | apple | |
020101 | pineapple | |
Company B | ||
020102 | pineapple | |
020102 | apple | |
020102 | grape | |
020102 | papaya | |
Company C |
How to transform it into below dataset? Just want the company to be first row instead of last row?
The no of items can be any number.
group_no | item_name | company |
020101 | banana | Company A |
020101 | apple | |
020101 | grape | |
020101 | apple | Company B |
020101 | pineapple | |
020102 | pineapple | Company C |
020102 | apple | |
020102 | grape | |
020102 | papaya |
Thank you in advance
Hello,
data have;
infile cards dlm=',' dsd;
format group_no z6. item_name $10. company $10.;
input group_no item_name company;
cards;
020101,banana,
020101,apple,
020101,grape,
., ,Company A
020101,apple,
020101,pineapple,
., ,Company B
020102,pineapple,
020102,apple,
020102,grape,
020102,papaya,
., ,Company C
;
run;
data have2(keep=group_no item_name compid) companies(keep=company compid);
set have;
retain compid;
no1=lag(group_no);
if _N_=1 or no1=. then do;
compid+1;
end;
if group_no=. then output companies;
else output have2;
run;
data want;
merge have2 companies;
by compid;
comp1=lag(company);
if company=comp1 then company=" ";
drop compid comp1;
run;
Hello,
data have;
infile cards dlm=',' dsd;
format group_no z6. item_name $10. company $10.;
input group_no item_name company;
cards;
020101,banana,
020101,apple,
020101,grape,
., ,Company A
020101,apple,
020101,pineapple,
., ,Company B
020102,pineapple,
020102,apple,
020102,grape,
020102,papaya,
., ,Company C
;
run;
data have2(keep=group_no item_name compid) companies(keep=company compid);
set have;
retain compid;
no1=lag(group_no);
if _N_=1 or no1=. then do;
compid+1;
end;
if group_no=. then output companies;
else output have2;
run;
data want;
merge have2 companies;
by compid;
comp1=lag(company);
if company=comp1 then company=" ";
drop compid comp1;
run;
data have;
infile cards dlm=',' dsd;
format group_no z6. item_name $10. company $10.;
input group_no item_name company;
cards;
020101,banana,
020101,apple,
020101,grape,
., ,Company A
020101,apple,
020101,pineapple,
., ,Company B
020102,pineapple,
020102,apple,
020102,grape,
020102,papaya,
., ,Company C
;
run;
data key;
set have;
if not missing(company);
keep company;
run;
data want;
set have;
call missing(company);
if missing(lag(group_no)) then set key;
if not missing(group_no);
run;
That means if lag value of group_no is missing ,then copy an obs/record from table KEY.
Note : it copy obs from KEY is one by one until the end of KEY .
There are reasonable single data step solutions:
data have;
infile cards dlm=',' dsd;
format group_no z6. item_name $10. company $10.;
input group_no item_name company;
cards;
020101,banana,
020101,apple,
020101,grape,
., ,Company A
020101,apple,
020101,pineapple,
., ,Company B
020102,pineapple,
020102,apple,
020102,grape,
020102,papaya,
., ,Company C
;
run;
data want (drop=_:);
set have;
if _n_=1 or group_no=. then
set have (keep=company rename=(company=_ref_value) where=(_ref_value^=''));
if group_no =. then delete;
else if _ref_value^=lag(_ref_value) then company=_ref_value;
run;
Hi mkeintz,
Thank for your code and help.
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 25. 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.