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

[sagulolo]

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

[gamotte · Accepted solution]

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;

[sagulolo]

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

[Ksharp]

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;

[sagulolo]

Hi Ksharp,

Thank for your code.

appreciated if you could explain more on part below:-

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

thanks in advance

[Ksharp]

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 .

[mkeintz]

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;

[sagulolo]

Hi mkeintz,

Thank for your code and help.