topic Re: PROC APPEND Adding data when one column is gone in SAS Programming

PROC APPEND Adding data when one column is gone

Gieorgie — Thu, 28 Oct 2021 09:21:50 GMT

I have a question about proc append. If I have such a table as below, which code I run every week and let's assume that next week I will not have any records, e.g. in 2021-12. Will it use proc append add data to me anyway and put 0 in the 2021-12 column. If not, what to use to avoid the situation that he will not give me another row of results because he will not find values in the previous month.

PROC SQL;   
   create table POLICY_VINTAGE_WEEKLY as
   select distinct 
   POLICY_VINTAGE
  ,count(NRB) as NUMBER_POLICY
  ,today() as Run_Date format=weeku.
   from PolisyEnd
   where POLIS ="A"
   group by 
   POLICY_VINTAGE
;
Quit;
proc append base=_work.policy_vintage_weekly data=policy_vintage_weekly;
run;

Re: PROC APPEND Adding data when one column is gone

Kurt_Bremser — Thu, 28 Oct 2021 10:34:05 GMT

The DISTINCT keyword is unnecessary, as you only have the GROUP BY variable, a summary result, and a fixed value in the SELECT. The only possible effect is bad performance.

The dataset created in the SQL does in no way match what's in the picture, so your question about the missing column does not make sense.

Re: PROC APPEND Adding data when one column is gone

Gieorgie — Thu, 28 Oct 2021 13:00:05 GMT

Thank you for your answer Kurt, this is a hypothetical assumption. I mean, when tomorrow I will run a same code and in the Policy_Vintage column with the date 2021-11 there will no longer be records, it will add POLICY_VINTAGE_WEEKLY a new position with the new Run_Date date. If so, whether in position 2021-01 will show 0 or empty cell ?

Re: PROC APPEND Adding data when one column is gone

Quentin — Thu, 28 Oct 2021 14:56:03 GMT

As @Kurt_Bremser mentioned, your picture doesn't match the code. Can you show your full code (after the SQL step, are you transposing the data to move your counts into columns)?

PROC APPEND will not create any records/values for you. It simply reads records from the DATA= dataset and appends them to the BASE= dataset.

Also, PROC APPEND cannot add variables/columns to the base dataset. So if you're you've got a base dataset with a column named NOV2021, and you use PROC APPEND to add records that have a new column DEC2021, the column won't be added. It will be ignored, or generate an error.

Generally it's a bad idea to store data in a wide format with weeks/months in columns. It's preferable to keep the data in the vertical format your SQL query.

If you describe more of your goal, and show more code, I think people will be able to make better suggestions.

Re: PROC APPEND Adding data when one column is gone

Kurt_Bremser — Thu, 28 Oct 2021 15:50:43 GMT

I don't deal in fairy tales. Please supply example data and the code you want to run against it, and the result you expect from that.

And if you ask íf something is possible, see Maxim 4.

Re: PROC APPEND Adding data when one column is gone

ballardw — Thu, 28 Oct 2021 21:57:02 GMT

Proc append by default will not append any data set with different structure than the base data set.

If you use the FORCE option a data set missing a column or with "extra" columns may append but the extra columns won't be added. Or if the type of the variable is different than in base.

A small example.

data base;
   input x y z;
datalines;
1 2 3
;

data to_add;
  input x y q;
datalines;
44 55  66
;

/* default behavior no append and warning for 
   each difference in data set
*/
proc append base=base data=to_add;
run;

/*force option warning and variable q not added*/
proc append base=base data=to_add force;
run;

/* and if you end up with different data types*/

data to_add2;
   input x y z$;
datalines;
333 555 888
;

proc append base=base data=to_add2 force;
run;

If you have an empty data set to append no observations are added.

data to_add3;
   set base (obs=0);
run;
proc append base=base data=to_add3 force;
run;

The above creates an empty data set from the structure of the base data as an example.

Your suspect variable name "2021-11" indicate that Append is not what you want. And if you want 0 in the rows without the 'new' value or previous columns you have lots of work.

You probably should consider that this is a bad data structure because any analysis will require changing variable names in code in many places. You would like be better off with a data structure like

Run_date (if this is even needed) Number_Policy Effective_date (which would be a date value with the year month) Value (or what ever the number 2188 9078 etc means for each month).