Hi,
I would like to obtain a dataset which has 3 columns: 'Stay_Date' / 'Prod_Class_ID' / 'Count'.
'Stay_Date' is 2015-03-02 through 2017-03-01; Prod_Class-ID is 1 through 3 for every Stay_Date; Count is an integer value.
In Table1, I obtained this one;
Stay_Date | Prod_Class_ID | Count
2015-03-02 | 2 | 4
2015-03-02 | 3 | 10
2015-03-03 | 2 | 5
2015-03-03 | 3 | 11
2015-03-04 | 1 | 1
2015-03-04 | 2 | 9
2015-03-04 | 3 | 12
....... | .... | ...
2017-03-01 | 1 | 6
2017-03-01 | 2 | 2
2017-03-01 | 3 | 5
The following dataset has missing value for (a) '2015-03-02' & Prod_Class_ID = 1 and (b) '2015-03-03' & Prod_Class_ID = 1
I know the Count for these two combinatios are 0. How can I make them also show up in the table?
My idea is to have the following code that all 'Stay_Date' s with 'Prod_Class_ID' s are 0:
Data WORK.Table2;
do STAY_DATE = '02MAR2015'd to '01MAR2017'd;
do PROD_CLASS_ID = 1 to 3;
Count = 0;
output;
end;
end;
FORMAT STAY_DATE yymmdd10.;
Run;
Output:
Stay_Date | Prod_Class_ID | Count
2015-03-02 | 1 | 0
2015-03-02 | 2 | 0
2015-03-02 | 3 | 0
2015-03-03 | 1 | 0
2015-03-03 | 2 | 0
2015-03-03 | 3 | 0
......... ......... ......
By Combining Table1 and Table2 using some Proc Sql procedures or Data Merge procedures and smething else, I obtained Table1 again. Can I ask how to write the code to make 'Count = 0' show up together with Table1 for the other dates & Prod_ClassID combinations? (Like Table3)
Table3:
Stay_Date | Prod_Class_ID | Count
2015-03-02 | 1 | 0
2015-03-02 | 2 | 4
2015-03-02 | 3 | 10
2015-03-03 | 1 | 0
2015-03-03 | 2 | 5
2015-03-03 | 3 | 11
2015-03-04 | 1 | 1
2015-03-04 | 2 | 9
2015-03-04 | 3 | 12
....... | .... | ...
2017-03-01 | 1 | 6
2017-03-01 | 2 | 2
2017-03-01 | 3 | 5
Thank you!
data stay;
infile cards dsd dlm='|' firstobs=2;
input Stay_Date :yymmdd. Prod_Class_ID Count;
format stay_date yymmdd.;
cards;
Stay_Date|Prod_Class_ID|Count
2015-03-02|2|4
2015-03-02|3|10
2015-03-03|2|5
2015-03-03|3|11
2015-03-04|1|1
2015-03-04|2|9
2015-03-04|3|12
2017-03-01|1|6
2017-03-01|2|2
2017-03-01|3|5
;;;;
run;
proc print;
run;
proc summary data=stay nway;
class prod:;
output out=classdata(drop=_:);
run;
proc print;
run;
proc summary data=stay classdata=classdata nway;
by stay:;
class prod:;
freq count;
output out=filled(rename=(_freq_=Count) drop=_type_);
run;
proc print;
run;
You have a number of options, but if your data are that symmetrical, your template idea is not bad at all. You'd then just need to merge on your existing data and use the coalesce function to populate missing values with zero. Something like this:
proc sql;
CREATE TABLE want AS
SELECT a.stay_date, a.prod_class,
COALESCE(b.count, 0) AS count
FROM table2 AS a /* Your template */
LEFT JOIN table1 AS b
ON a.stay_date = b.stay_date AND
a.prod_class = b.prod_class;
quit;
Hi,
Thank you and I tried that, it returned all Stay dates from '2015-03-02' to '2017-03-01' with Counts 0.
Search PRELOADFMT, either on here, Google or LexJansen.com
PRELOADFMT allows you to add in values that don't originate in your dataset.
The other common solution is the one proposed by @collinelliot and if it didn't work post your exact code and log. I suspect you may have accidently gotten a variable name wrong or something since it should work.
data stay;
infile cards dsd dlm='|' firstobs=2;
input Stay_Date :yymmdd. Prod_Class_ID Count;
format stay_date yymmdd.;
cards;
Stay_Date|Prod_Class_ID|Count
2015-03-02|2|4
2015-03-02|3|10
2015-03-03|2|5
2015-03-03|3|11
2015-03-04|1|1
2015-03-04|2|9
2015-03-04|3|12
2017-03-01|1|6
2017-03-01|2|2
2017-03-01|3|5
;;;;
run;
proc print;
run;
proc summary data=stay nway;
class prod:;
output out=classdata(drop=_:);
run;
proc print;
run;
proc summary data=stay classdata=classdata nway;
by stay:;
class prod:;
freq count;
output out=filled(rename=(_freq_=Count) drop=_type_);
run;
proc print;
run;
An example with PRELOADFMT
data stay; infile cards dsd dlm='|' firstobs=2; input Stay_Date :yymmdd. Prod_Class_ID Count; format stay_date yymmdd10.; cards; Stay_Date|Prod_Class_ID|Count 2015-03-02|2|4 2015-03-02|3|10 2015-03-03|2|5 2015-03-03|3|11 2015-03-04|1|1 2015-03-04|2|9 2015-03-04|3|12 2017-03-01|1|6 2017-03-01|2|2 2017-03-01|3|5 ;;;; run; proc format library=work; value prod 1='1' 2='2' 3='3' ; run; proc summary data=stay nway completetypes; class Stay_Date; class prod: /preloadfmt ; format prod: prod.; freq count; output out=classdata(rename=(_freq_=Count) drop=_type_); 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 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.