topic Re: Retain observations in SAS Programming

Retain observations

sascode — Wed, 05 Apr 2023 17:34:03 GMT

Hello,

I have a dataset as follows:

data have;
input Id Date :anydtdte. FirstName $ LastName $ x ;
format date monyy6. ;
datalines;
123 Apr-22 A B 20
123 Apr-23 A B 30
123 Apr-24 A B 40
124 Apr-22 E F 10
125 Apr-22 G H 10
125 Apr-23 G H 10

;

As i am showing, ID 124 and125 are not present in all dates. How do i add 2 more rows for id 124 and 1 more row for ID 125, and having x just missing?
Thank you.

Re: Retain observations

Venkatram — Wed, 05 Apr 2023 18:07:24 GMT

Retian process is id 124and 125 based on condition

Re: Retain observations

sascode — Wed, 05 Apr 2023 18:11:12 GMT

Yes, because these IDs have not data for all 3 dates

Re: Retain observations

Seadrago — Wed, 05 Apr 2023 19:57:20 GMT

So I think you'll want a dataset with all possible combinations of ID and DATE

proc sort data=have NODUPKEY out=uniq_id(keep=id); by id; run;

proc sort data=have NODUPKEY out=uniq_date(keep=date); by date; run;

proc sql;

create table all_comb as

select uniq_id.id, uniq_date.date

from uniq_id cross join uniq_date;

quit;

/*merge all possible combinations with original dataset*/

proc sort data=have; by id date; run;

proc sort data=all_comb; by id date; run;

data want;

merge all_comb(in=a) have;

by id date;

if a;

run;

Re: Retain observations

sascode — Wed, 05 Apr 2023 23:36:24 GMT

Hi,
first thank you your help,
Your code worked and it successfully retained the ID and date, but it did not retain Firstname and Lastname, and instead, FirstName and LastName are empty.
Thank you.

Re: Retain observations

Kurt_Bremser — Thu, 06 Apr 2023 05:46:53 GMT

Are your dates meant to be April 1st in different years, or multiple days in April in a given year?

If the first, which century are those years in, if the second, which year?

Re: Retain observations

Kathryn_SAS — Thu, 06 Apr 2023 10:46:52 GMT

You could use PROC SUMMARY with COMPLETETYPES to get the desired results.

data have;
input Id Date :anydtdte. FirstName $ LastName $ x ;
format date monyy6. ;
datalines;
123 Apr-22 A B 20
123 Apr-23 A B 30
123 Apr-24 A B 40
124 Apr-22 E F 10
125 Apr-22 G H 10
125 Apr-23 G H 10
;
run;

proc summary data=have completetypes nway;
class id date;
id firstname lastname;
var x;
output out=want(drop=_type_ _freq_) sum=;
run;

data want;
set want;
if firstname ne ' ' then temp=firstname;
else firstname=temp;
if lastname ne ' ' then temp1=lastname;
else lastname=temp1;
retain temp temp1;
drop temp temp1;
run;

proc print data=want;
run;

Re: Retain observations

Ksharp — Thu, 06 Apr 2023 11:48:41 GMT

data have;
input Id Date :anydtdte. FirstName $ LastName $ x ;
format date monyy6. ;
datalines;
123 Apr-22 A B 20
123 Apr-23 A B 30
123 Apr-24 A B 40
124 Apr-22 E F 10
125 Apr-22 G H 10
125 Apr-23 G H 10
;


proc sql;
create table temp as
select a.*,b.FirstName,b.LastName,b.x
 from 
(select * from (select distinct id from have),(select distinct Date from have)) as a
natural left join have as b
order by id,date;
quit;
data want;
 update temp(obs=0) temp;
 by id;
 output;
run;

Re: Retain observations

sascode — Thu, 06 Apr 2023 13:09:29 GMT

Hi, i agree, however, since we know we are adding additional rows for these id's with not completed dates,
can we set x to missing for these addition rows?
Thank you .

Re: Retain observations

Kathryn_SAS — Thu, 06 Apr 2023 13:17:25 GMT

If you run my code that uses PROC SUMMARY, X is set to missing for the additional rows.

Re: Retain observations

Ksharp — Fri, 07 Apr 2023 11:41:31 GMT

/*
Sure just need another data step.
*/
data have;
input Id Date :anydtdte. FirstName $ LastName $ x ;
format date monyy6. ;
datalines;
123 Apr-22 A B 20
123 Apr-23 A B 30
123 Apr-24 A B 40
124 Apr-22 E F 10
125 Apr-22 G H 10
125 Apr-23 G H 10
;


proc sql;
create table temp as
select a.*,b.FirstName,b.LastName,b.x
 from 
(select * from (select distinct id from have),(select distinct Date from have)) as a
natural left join have as b
order by id,date;
quit;
data want;
 update temp(obs=0 drop=x) temp(drop=x);
 by id;
 output;
run;
data final_want;
 merge want temp(keep= id date x);
 by id date;
run;