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.
/*
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;
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;
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?
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;
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;
If you run my code that uses PROC SUMMARY, X is set to missing for the additional rows.
/*
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: