topic Re: How to add new columns to dataset each time I run my code in SAS Programming

How to add new columns to dataset each time I run my code

Gieorgie — Wed, 27 Oct 2021 13:47:36 GMT

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_WEEKLY as
   select distinct 
   POLICY_VINTAGE
  ,count(NRB) as Week <----- here so that week has a consecutive number, e.g. once week1, then week2
   from PolisyEnd
   where POLIS ="A"
   group by 
   POLICY_VINTAGE

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

Re: How to add new columns to dataset each time I run my code

ballardw — Wed, 27 Oct 2021 14:05:43 GMT

I'm afraid that you will need to provide an example of a data set and what you expect to get for a good answer.

If you expect to get a different variable name then that is an indication of poor design as everything down stream from this would need to have code rewritten.

Do you have any DATE value involved with this data?

Re: How to add new columns to dataset each time I run my code

Reeza — Wed, 27 Oct 2021 14:27:29 GMT

*run each week;
PROC SQL;   
   create table POLICY_VINTAGE_WEEKLY as
   select distinct 
   POLICY_VINTAGE
  ,count(NRB) as Count,
  today() as Run_Date format=weeku.
   from PolisyEnd
   where POLIS ="A"
   group by 
   POLICY_VINTAGE

;
Quit;

proc append base=policy_vintage data=policy_vintage_weekly;
run;

Consider instead adding rows and a column to identify each week.

If you really want a wide structure then take your policy_vintage and transpose it as needed for reports.

General principal -> It's easier and faster to add rows to tables, than it is columns. It also takes up less space.

@Gieorgie wrote:

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_WEEKLY as
   select distinct 
   POLICY_VINTAGE
  ,count(NRB) as Week <----- here so that week has a consecutive number, e.g. once week1, then week2
   from PolisyEnd
   where POLIS ="A"
   group by 
   POLICY_VINTAGE

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

Re: How to add new columns to dataset each time I run my code

Gieorgie — Wed, 27 Oct 2021 15:11:38 GMT

Is there posibility to change columns with date to Row. For example i have first columns with date 2021-01 etc to make like a headers

Re: How to add new columns to dataset each time I run my code

Reeza — Wed, 27 Oct 2021 15:14:21 GMT

Yes, use PROC TRANSPOSE. I would label them as DATE_YYYYMMDD and put the date as a label. Or even convert it to week numbers using the WEEK() function.

Re: How to add new columns to dataset each time I run my code

Gieorgie — Thu, 28 Oct 2021 06:31:23 GMT

Thanks Reeza, if I understand correctly, when I play this code next week next to Count there will be a new column count. Because now when I play it doesn't get a new column, it's because it's still the same week

Re: How to add new columns to dataset each time I run my code

Reeza — Thu, 28 Oct 2021 15:27:56 GMT

Nope, it won't magically add a new column. The process you seem to be doing is one where you will be running this each week and adding it to a master file.
If you're just running it each week and restating your data, then you need a different approach. In that case, do you have a date column? Or how do you know which data belongs to which week?
I think you need to explain your use case a bit more.