DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Contributor
Posts: 63
Accepted Solution

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

Dear All,

 

I got a dataset as per below:-

 

group_noitem_namecompany
020101banana 
020101apple 
020101grape 
  Company A
020101apple 
020101pineapple 
  Company B
020102pineapple 
020102apple 
020102grape 
020102papaya 
  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_noitem_namecompany
020101bananaCompany A
020101apple 
020101grape 
020101appleCompany B
020101pineapple 
020102pineappleCompany C
020102apple 
020102grape 
020102papaya 

 

Thank you in advance


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;

View solution in original post


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,

Thank for your code.

appreciated if you could explain more on part below:-

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

thanks in advance
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 .

Trusted Advisor
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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 270 views
  • 4 likes
  • 4 in conversation