Solved
Contributor
Posts: 63

# how to shift the last row company name data into first row

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

Accepted Solutions
Solution
‎11-16-2017 05:07 AM
Super Contributor
Posts: 359

## Re: how to shift the last row company name data into first row

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

All Replies
Solution
‎11-16-2017 05:07 AM
Super Contributor
Posts: 359

## Re: how to shift the last row company name data into first row

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;``````
Contributor
Posts: 63

## Re: how to shift the last row company name data into first row

Hi gamotte,

Thank you so much, it help me a lot and it totally out of my mind, it can be done by this way.

Best regards and thank for your time
Super User
Posts: 10,849

## Re: how to shift the last row company name data into first row

``````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;``````
Contributor
Posts: 63

## Re: how to shift the last row company name data into first row

Hi Ksharp,

appreciated if you could explain more on part below:-

"if missing(lag(group_no)) then set key;"

Super User
Posts: 10,849

## Re: how to shift the last row company name data into first row

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 .

Posts: 1,389

## Re: how to shift the last row company name data into first row

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;
``````
Contributor
Posts: 63

## Re: how to shift the last row company name data into first row

Hi mkeintz,

Thank for your code and help.

☑ This topic is solved.