topic bring don observations (retain) , By group in SAS Programming

bring don observations (retain) , By group

seancho — Thu, 11 Jun 2020 17:39:31 GMT

Dear all,

I have a 2020 product list, with detailed information. I vertically joined a 2020 list with older lists (2019,2018 etc.), so I have all the products ever produced, not just 2020 current products.

My goal is to get one observation for one ID, with all information.

2020 list file:

ID detailed info1(have more) _2020 (indicator variable to show the product is in 2020 list)

A101 Germany 1

B101 China 1

older file : ex)2019

ID 2019

A101 1

C101 1

Combined list , sorted by ID (I have it to this step)

ID detailed 1 2020 2019 2018

A101 Germany 1

A101 1

B101 China 1

B101 1

C101 1

PROBLEM:

I want to make it look like; (tried retain but failed)

ID detailed 1 -10 2020 2019 2018

A101 Germany 1

A101 Germany 1 1

A101 Germany 1 1 1

Then For final dataset:

then using (last.ID ?) so I have one ID obs with most information

A101 Germany 1 1 1

B101 China 1 1

C101 1 1

data product_sort;
	set product;
	by ID;

	if first.ID then do;
	

	if detailed_info_1 ne . then temp_1 = detailed_info_1;
	else if detailed_info_1 = . then detailed_info_1 = temp1;
	retain temp1;

	end;



/*keep only last symbol */

run;

Thank you so much for your help in advance!

Re: bring don observations (retain) , By group

novinosrin — Thu, 11 Jun 2020 17:47:32 GMT


data have;
input ID    $    detailed1   $   _2020 _2019 _2018 ;
cards;
A101   Germany          1 . .

A101      .              .             1 .

A101        . . .                                      1
B101    China             1 . .

B101         . .                           1 .

C101       . .                             1 .

C101     . . .                                         1
;

data want;
update have(obs=0) have;
by id;
run;

Re: bring don observations (retain) , By group

smantha — Thu, 11 Jun 2020 18:01:45 GMT

You can use a proc summary and do a max or proc means with a max and get the result.

proc means data = have nway missing noprint;

class ID;

var _:;

output out=want(drop=_type_ _freq_) max=;

run;

Re: bring don observations (retain) , By group

smantha — Thu, 11 Jun 2020 18:04:08 GMT

proc means data = have nway missing noprint;

class ID detailed_1  ;

var _:;

output out=want(drop=_type_ _freq_) max=;

run;

Re: bring don observations (retain) , By group

seancho — Thu, 11 Jun 2020 18:13:33 GMT

Thank you very much!!!