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;
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;
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;
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;
Is that @s_lassen code what you are looking for ?
Yes! I don't get the subtle difference though. Two separate select statements vs
having them both in one, separated by a comma...
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
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?
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 .
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 25. 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.