BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Crubal
Quartz | Level 8

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!

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19
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;

Capture.PNG

View solution in original post

5 REPLIES 5
collinelliot
Barite | Level 11

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;
Crubal
Quartz | Level 8

Hi, 

 

Thank you and I tried that, it returned all Stay dates from '2015-03-02' to '2017-03-01' with Counts 0. 

Reeza
Super User

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_null__
Jade | Level 19
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;

Capture.PNG

ballardw
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1112 views
  • 0 likes
  • 5 in conversation