topic Re: Fill in rows for all dates in SAS Programming

Fill in rows for all dates

crawfe — Fri, 13 Sep 2019 01:23:33 GMT

Hi. What is the protocol for follow-up questions if you have already marked an answer as a Solution?

Start another entry? Anyway, KSHARP posted a solution for me that I can't expand successfully on (add another column).

I have a table of NAME1-Values responses by month/year. I need to fill in rows around them to

cover 11/2017 to the present month/year with a 0 value.

Unfortunately, I get all combinations of NAME and LABEL with the filled-in rows.

I only want the LABELS that go with NAME1 (see the bottom - LABEL13 should only be with NAME2).

I hope that is clear enough. LABEL1 just needs to be duplicated 22 times (i.e. NAME1-LABEL1 (22 date rows), NAME1-LABEL2 (22 date rows), NAME1-LABEL4 (22 rows) etc.). (Sometimes there is more than one LABEL column if that matters. They all just need

to be duplicated 22 times with their associated NAME....)

Thanks!

EXAMPLE FAILING OUTPUT:

name label date value
NAME1 LABEL1 11/2017 0
NAME1 LABEL1 12/2017 2
NAME1 LABEL1 01/2018 0
NAME1 LABEL1 02/2018 0
NAME1 LABEL1 03/2018 0
NAME1 LABEL1 04/2018 0
NAME1 LABEL1 05/2018 0
NAME1 LABEL1 06/2018 0
NAME1 LABEL1 07/2018 0
NAME1 LABEL1 08/2018 0
NAME1 LABEL1 09/2018 0
NAME1 LABEL1 10/2018 0
NAME1 LABEL1 11/2018 0
NAME1 LABEL1 12/2018 0
NAME1 LABEL1 01/2019 0
NAME1 LABEL1 02/2019 0
NAME1 LABEL1 03/2019 0
NAME1 LABEL1 04/2019 0
NAME1 LABEL1 05/2019 0
NAME1 LABEL1 06/2019 0
NAME1 LABEL1 07/2019 0
NAME1 LABEL1 08/2019 0
NAME1 LABEL1 09/2019 0
NAME1 LABEL13 11/2017 0
NAME1 LABEL13 12/2017 0
NAME1 LABEL13 01/2018 0

/* I have added code to code from KSHARP */

data have;
input name $ label $ _date $ value;
date=input(cats('01/',_date),ddmmyy10.); /* CATS removes all blanks */
drop _:;
format date mmyys7.;
datalines;
NAME1 LABEL1 12/2017 2
NAME1 LABEL2 07/2018 1
NAME1 LABEL4 02/2019 3
NAME2 LABEL13 01/2018 3
NAME2 LABEL20 11/2018 4
NAME2 LABEL25 07/2019 1
;

data date1; /* this routine gives a column of dates */
do date='01nov2017'd to TODAY(); /* '01jul2019'd; */
if month(date) ne month then output;
month=month(date);
end;
drop month;
format date mmyys7.;
run;

proc sql; /* JOIN date column to Name and value */
create table want as
select a.*,coalesce(b.value,0) as value
from (select * from
(select distinct name from have),(select distinct label from have), (select date from date1)
) as a left join have as b
on a.name=b.name AND a.label=b.label AND a.date=b.date ;
quit;

Re: Fill in rows for all dates

Kurt_Bremser — Fri, 13 Sep 2019 07:26:53 GMT

Try this:

data have;
input name $ label $ _date $ value;
date=input(cats('01/',_date),ddmmyy10.); /* CATS removes all blanks */
drop _:;
format date mmyys7.;
datalines;
NAME1 LABEL1 12/2017 2
NAME1 LABEL2 07/2018 1
NAME1 LABEL4 02/2019 3
NAME2 LABEL13 01/2018 3
NAME2 LABEL20 11/2018 4
NAME2 LABEL25 07/2019 1
;

proc sort
  data=have (keep=name label)
  out=ref1
  nodupkey
;
by name label;
run;

data ref2;
set ref1;
date = input("2017-12-01",yymmdd10.);
do while (date le today());
  output;
  date = intnx('month',date,1);
end;
run;

data want;
merge
  have (in=a)
  ref2 (in=b)
;
by name label date;
if b;
if not a then value = 0;
run;

Re: Fill in rows for all dates

s_lassen — Fri, 13 Sep 2019 11:23:01 GMT

Not quite sure if that's what you want, but it seems that you could just change one line in the final SQL:

proc sql; /* JOIN date column to Name and value */
create table want as
select a.*,coalesce(b.value,0) as value
from (select * from
(select distinct name,label from have), (select date from date1) /* this is changed! */
) as a left join have as b
on a.name=b.name AND a.label=b.label AND a.date=b.date ;
quit;

Re: Fill in rows for all dates

Ksharp — Fri, 13 Sep 2019 11:47:59 GMT

Is that @s_lassen code what you are looking for ?

Re: Fill in rows for all dates

crawfe — Fri, 13 Sep 2019 12:19:36 GMT

Yes! I don't get the subtle difference though. Two separate select statements vs

having them both in one, separated by a comma...

Re: Fill in rows for all dates

Ksharp — Fri, 13 Sep 2019 12:28:13 GMT

Can you post where is not right ?

name	label	date	value
NAME1	LABEL1	11/2017	0
NAME1	LABEL1	12/2017	2
NAME1	LABEL1	01/2018	0
NAME1	LABEL1	02/2018	0
NAME1	LABEL1	03/2018	0
NAME1	LABEL1	04/2018	0
NAME1	LABEL1	05/2018	0
NAME1	LABEL1	06/2018	0
NAME1	LABEL1	07/2018	0
NAME1	LABEL1	08/2018	0
NAME1	LABEL1	09/2018	0
NAME1	LABEL1	10/2018	0
NAME1	LABEL1	11/2018	0
NAME1	LABEL1	12/2018	0
NAME1	LABEL1	01/2019	0
NAME1	LABEL1	02/2019	0
NAME1	LABEL1	03/2019	0
NAME1	LABEL1	04/2019	0
NAME1	LABEL1	05/2019	0
NAME1	LABEL1	06/2019	0
NAME1	LABEL1	07/2019	0
NAME1	LABEL1	08/2019	0
NAME1	LABEL1	09/2019	0
NAME1	LABEL2	11/2017	0
NAME1	LABEL2	12/2017	0
NAME1	LABEL2	01/2018	0
NAME1	LABEL2	02/2018	0
NAME1	LABEL2	03/2018	0
NAME1	LABEL2	04/2018	0
NAME1	LABEL2	05/2018	0
NAME1	LABEL2	06/2018	0
NAME1	LABEL2	07/2018	1
NAME1	LABEL2	08/2018	0
NAME1	LABEL2	09/2018	0
NAME1	LABEL2	10/2018	0
NAME1	LABEL2	11/2018	0
NAME1	LABEL2	12/2018	0
NAME1	LABEL2	01/2019	0
NAME1	LABEL2	02/2019	0
NAME1	LABEL2	03/2019	0
NAME1	LABEL2	04/2019	0
NAME1	LABEL2	05/2019	0
NAME1	LABEL2	06/2019	0
NAME1	LABEL2	07/2019	0
NAME1	LABEL2	08/2019	0
NAME1	LABEL2	09/2019	0
NAME1	LABEL4	11/2017	0
NAME1	LABEL4	12/2017	0
NAME1	LABEL4	01/2018	0
NAME1	LABEL4	02/2018	0
NAME1	LABEL4	03/2018	0
NAME1	LABEL4	04/2018	0
NAME1	LABEL4	05/2018	0
NAME1	LABEL4	06/2018	0
NAME1	LABEL4	07/2018	0
NAME1	LABEL4	08/2018	0
NAME1	LABEL4	09/2018	0
NAME1	LABEL4	10/2018	0
NAME1	LABEL4	11/2018	0
NAME1	LABEL4	12/2018	0
NAME1	LABEL4	01/2019	0
NAME1	LABEL4	02/2019	3
NAME1	LABEL4	03/2019	0
NAME1	LABEL4	04/2019	0
NAME1	LABEL4	05/2019	0
NAME1	LABEL4	06/2019	0
NAME1	LABEL4	07/2019	0
NAME1	LABEL4	08/2019	0
NAME1	LABEL4	09/2019	0
NAME2	LABEL13	11/2017	0
NAME2	LABEL13	12/2017	0
NAME2	LABEL13	01/2018	3
NAME2	LABEL13	02/2018	0
NAME2	LABEL13	03/2018	0
NAME2	LABEL13	04/2018	0
NAME2	LABEL13	05/2018	0
NAME2	LABEL13	06/2018	0
NAME2	LABEL13	07/2018	0
NAME2	LABEL13	08/2018	0
NAME2	LABEL13	09/2018	0
NAME2	LABEL13	10/2018	0
NAME2	LABEL13	11/2018	0
NAME2	LABEL13	12/2018	0
NAME2	LABEL13	01/2019	0
NAME2	LABEL13	02/2019	0
NAME2	LABEL13	03/2019	0
NAME2	LABEL13	04/2019	0
NAME2	LABEL13	05/2019	0
NAME2	LABEL13	06/2019	0
NAME2	LABEL13	07/2019	0
NAME2	LABEL13	08/2019	0
NAME2	LABEL13	09/2019	0
NAME2	LABEL20	11/2017	0
NAME2	LABEL20	12/2017	0
NAME2	LABEL20	01/2018	0
NAME2	LABEL20	02/2018	0
NAME2	LABEL20	03/2018	0
NAME2	LABEL20	04/2018	0
NAME2	LABEL20	05/2018	0
NAME2	LABEL20	06/2018	0
NAME2	LABEL20	07/2018	0
NAME2	LABEL20	08/2018	0
NAME2	LABEL20	09/2018	0
NAME2	LABEL20	10/2018	0
NAME2	LABEL20	11/2018	4
NAME2	LABEL20	12/2018	0
NAME2	LABEL20	01/2019	0
NAME2	LABEL20	02/2019	0
NAME2	LABEL20	03/2019	0
NAME2	LABEL20	04/2019	0
NAME2	LABEL20	05/2019	0
NAME2	LABEL20	06/2019	0
NAME2	LABEL20	07/2019	0
NAME2	LABEL20	08/2019	0
NAME2	LABEL20	09/2019	0
NAME2	LABEL25	11/2017	0
NAME2	LABEL25	12/2017	0
NAME2	LABEL25	01/2018	0
NAME2	LABEL25	02/2018	0
NAME2	LABEL25	03/2018	0
NAME2	LABEL25	04/2018	0
NAME2	LABEL25	05/2018	0
NAME2	LABEL25	06/2018	0
NAME2	LABEL25	07/2018	0
NAME2	LABEL25	08/2018	0
NAME2	LABEL25	09/2018	0
NAME2	LABEL25	10/2018	0
NAME2	LABEL25	11/2018	0
NAME2	LABEL25	12/2018	0
NAME2	LABEL25	01/2019	0
NAME2	LABEL25	02/2019	0
NAME2	LABEL25	03/2019	0
NAME2	LABEL25	04/2019	0
NAME2	LABEL25	05/2019	0
NAME2	LABEL25	06/2019	0
NAME2	LABEL25	07/2019	1
NAME2	LABEL25	08/2019	0
NAME2	LABEL25	09/2019	0

Re: Fill in rows for all dates

crawfe — Fri, 13 Sep 2019 14:43:38 GMT

That is the correct output now. I was wondering about this code correction.

This Gave all the combinations of name-label:

(select distinct name from have),(select distinct label from have), (select date from date1)
)

vs.

This Gave only desired combinations of name-label:

(select distinct name,label from have), (select date from date1) /* this is changed! */
)

I was wondering how does SAS evaluate this differently?

Re: Fill in rows for all dates

Ksharp — Sat, 14 Sep 2019 10:27:32 GMT

The first SQL gave you all the combination of name ,label and date from the table .

The second SQL gave you all the combination of new variable (=cats(name,label) and date from the table .

You can make some dummy data to test the difference between these two codes by yourself .