SQL How to add new columns each time I run my code-CodePudding

I would like that every time I run the code, the POLICY_VINTAGE dataset would add count (NRB) result from processing as another column called eg Week2 and next week3. Is there any additional feature in SAS for that?

PROC SQL;   
   create table POLICY_VINTAGE as
   select distinct 
   POLICY_VINTAGE
  ,count(NRB) as Week1
   from PolisyEnd
   where POLIS ="A"
   group by 
   POLICY_VINTAGE

;
Quit;
data POLICY_VINTAGE ;
set _work.POLICY_VINTAGE 
;
run;

CodePudding user response：

To do this, you'll need to merge your historical dataset with the new dataset. It would be much more efficient to store your historical data in a long format, and present it in a wide format as needed.

First, to answer your question, let's create some code that does exactly what you asked. Your initial historical dataset will be called policy_vintage_historical, and all new data will merge to it.

Since you would be modifying a permanent dataset by merging things to it, it may be a good idea to use the genmax option to create backups of your dataset automatically. Keep in mind, if your dataset is big, this may not be feasible and you do not want to use this.

data lib.policy_vintage_historical;
    merge lib.policy_vintage_historical
              policy_vintage
    ;

    by policy_vintage;
run;

A more efficient way: store it in a long format. Note that the week number needs to dynamically be generated. You can do this in any way you'd like.

The code below will calculate the total, then append it to a long dataset.

PROC SQL;   
   create table POLICY_VINTAGE as
       select distinct 
            POLICY_VINTAGE
          , 1 as week
          , count(nrb) as total
       from PolisyEnd
       where POLIS ="A"
       group by 
       POLICY_VINTAGE
quit;

proc append base=lib.policy_vintage_historical
            data=policy_vintage
            force;
run;

If you want to display this in a long format, use proc transpose. Note that you will need to either sort or index policy_vintage within lib.policy_vintage_historical before transposing.

proc transpose data   = lib.policy_vintage_historical
               out    = policy_vintage_tpose(drop=_NAME_)
               prefix = Week;
    by policy_vintage;
    id week;
    var Total;
run;