Retain observations

[sascode]

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.

[Venkatram]

Retian process is id 124and 125 based on condition

[sascode]

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

[Seadrago]

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;

[sascode]

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.

[Kurt_Bremser]

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?

[Kathryn_SAS]

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;

[Ksharp]

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;

[sascode]

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 .

[Kathryn_SAS]

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

[Ksharp · Accepted solution]

/*
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;