topic Re: PROC Report Problem with column name in SAS Programming

PROC Report Problem with column name

Gieorgie — Fri, 29 Oct 2021 07:30:55 GMT

I would like to build a proc report from my date, where it would calculate html / excel mean, median, sum etc. as a separate document. However, the problem is that from the table where I have POLICY_VINTAGE dates like this on below :

I have to (probably best create) the widetable with PROC TRANSPOSE. With this code,

PROC SQL;   
   create table POLICY_VINTAGE_WEEKLY as
   select 
   POLICY_VINTAGE
  ,count(NRB) as NUMBER_POLICY
  ,2 as Run_Date 
   from PolisyEnd
   where POLIS ="W"
   group by 
   POLICY_VINTAGE
;
Quit;
proc transpose data   = policy_vintage_weekly
               name= Polisy_Active
               out    = work.policy_vintage_weekly;
	   by Run_Date;
    id policy_vintage;

then the table looks like this:

When I try to build proc reports I get the error that POLICY_VINTAGE columns are missing, is it because of proc transpose? Do I have to choose all columns? How to make him calculate these indicators for me from the tabe

proc report data= _work.policy_vintage_weekly;
column POLICY_VINTAGE NUMBER_POLICY;
define NUMBER_POLICY/sum;
define NUMBER_POLICY/mean;
run;

ERROR: The variable POLICY_VINTAGE is not in _WORK.POLICY_VINTAGE_WEEKLY.

Re: PROC Report Problem with column name

ballardw — Fri, 29 Oct 2021 08:58:17 GMT

Mean, median or sum of what exactly? Your data description doesn't provide any likely candidates to do summary statistics on.

If you have a numeric variable that you want then no need to use proc sql

Using a data set you should have available this shows how to get a count sum and mean of a variable under columns of another variable:

proc report data=sashelp.class;
   columns sex age,( (n sum mean),weight) ;
   define sex / group;
   define age /across;
run;

It would really help to show some actual example data and what the report is supposed to look like, as in expected structure and maybe small number of values if you can do them by hand.

Your error is because when you use Policy_vintage as an ID variable in proc transpose the values are used in creating names of columns and the variable Policy_vintage, as you even show in the picture, is not in the output data set Policy_vintage_weekly. So it is not there to use in Proc report. You may also want to look at your log

Re: PROC Report Problem with column name

Gieorgie — Fri, 29 Oct 2021 09:14:33 GMT

Hi, thanks for answear.

This is my sample data : And i would like create reports where the mean and median would be calculated for each date. I don't know how to create a variable for POLICY_VINTAGE if after transpose it changed the columns to the rotated dates.

Re: PROC Report Problem with column name

Kurt_Bremser — Fri, 29 Oct 2021 09:41:50 GMT

DO NOT TRANSPOSE YOUR DATA.

Maxim 19: Long Beats Wide.

Let PROC REPORT do that on its own:

proc report data=policy_vintage_weekly;
column run_date policy_vintage,number_policy;
define run_date / group;
define policy_vintage / across;
define number_policy / sum;
run;

This is just the most basic REPORT code, the procedure has a gazillion of options to get the report into shape.

For detailed help, provide usable example data (your original source, not the useless transposed data) in a data step with datalines, posted in a code box, and an example for the report you want.

Re: PROC Report Problem with column name

ballardw — Fri, 29 Oct 2021 09:45:48 GMT

@Gieorgie wrote:

Hi, thanks for answear.

This is my sample data : And i would like create reports where the mean and median would be calculated for each date. I don't know how to create a variable for POLICY_VINTAGE if after transpose it changed the columns to the rotated dates.

That's a picture, not data. As a minimum data is text with some description. Better is a data step that creates data similar to yours. You show a data set in the first step.

 from PolisyEnd

that is probably the one you should share.

Andy you have not shown 1) what the desired output is or 2) what you actually want to take a count, mean, sum or other summary of.

You may have made a very common mistake of assuming a process needs a specific step, such as Proc Transpose, when it very well may not.

Re: PROC Report Problem with column name

Gieorgie — Fri, 29 Oct 2021 10:05:24 GMT

This is date from PolisEnd some sample. And Run_Date is a release date of the report, it will be for a week. I want to count the medians and mean NUMBER_POLICY for each date.

POLICY_VINTAGE	NUMBER_POLICY	Run_Date
2008-11	1	10-29-2021
2010-01	3	10-29-2021
2010-03	1	10-29-2021
2010-05	1	10-29-2021
2010-06	2	10-29-2021

And this is sample data from tranpose I decided to transpose because it seems that it is better to collect the results of running the code, where run date is the run date and here I would also like to do mean / median for each date

Run_Date	Polisy_Active	2008-11	2010-01	2010-03	2010-05
1	NUMBER_POLICY	1	3	1	1
2	NUMBER_POLICY	1	3	1	1
2	NUMBER_POLICY	1	3	1	1
22582	NUMBER_POLICY	1	3	1	1
22582	NUMBER_POLICY	1	3	1	1

Re: PROC Report Problem with column name

Kurt_Bremser — Fri, 29 Oct 2021 10:30:24 GMT

The value of "NUMBER_POLICY" is obviously redundant in your example, so this item should get another place in your report.

When we say "data step with datalines", we mean THIS:

data have;
input POLICY_VINTAGE : yymmdd10. NUMBER_POLICY Run_Date : mmddyy10.;
format POLICY_VINTAGE yymmd7. Run_Date yymmdd10.;
datalines;
2008-11-01 1 10-29-2021
2010-01-01 3 10-29-2021
2010-03-01 1 10-29-2021
2010-05-01 1 10-29-2021
2010-06-01 2 10-29-2021
;

Everybody can recreate the dataset with a simple copy/paste and submit, without having to second-guess attributes and raw values. We expect you to use this in the future, as a most basic courtesy towards those that are here to help you, for no gain expect the pure fun of it. MAKE IT fun for us.

Now, from this you can run the following PROC REPORT:

proc report data=have;
column run_date number_policy,policy_vintage;
define run_date / group;
define policy_vintage / "Policy_Vintage" across;
define number_policy / "" sum;
run;

which results in this:

 	Policy_Vintage
Run_Date	2008-11	2010-01	2010-03	2010-05	2010-06
2021-10-29	1	3	1	1	2

Re: PROC Report Problem with column name

Gieorgie — Fri, 29 Oct 2021 16:19:49 GMT

I am doing transpose because I want to use proc append to add more results to the main table. However, when I don't use proc transpose. The table looks like this.

POLICY_VINTAGE	NUMBER_POLICY	Run_Date
2021-01	77	1
2021-02	101	1
2021-01	77	2
2021-02	101	2

I would like add to _work.policy_vinatge to be added every time I run the code, the tables next to it and not as subsequent lines, Some like a below :

POLICY_VINTAGE	1	2
2021-01	77	77
2021-02	101	101
2021-01	77	77
2021-02	101	101

Re: PROC Report Problem with column name

Kurt_Bremser — Fri, 29 Oct 2021 20:07:00 GMT

You simply add the new data to the base table and let PROC REPORT do its work. There is no need to append transposed data (which is not possible anyway, you need to completely rewrite the dataset with a data step).

Re: PROC Report Problem with column name

Gieorgie — Sat, 30 Oct 2021 07:22:48 GMT

Ok, but when I add new data to a historical file with proc append, it adds
me as new lines and I wish it would add as a new column. How do I change
my code?

Re: PROC Report Problem with column name

Gieorgie — Sat, 30 Oct 2021 07:23:06 GMT

Ok, but when I add new data to a historical file with proc append, it adds me as new lines and I wish it would add as a new column. How do I change my code?

Re: PROC Report Problem with column name

Kurt_Bremser — Sat, 30 Oct 2021 07:43:47 GMT

You always keep your historical data in the vertical layout, append to it, and then create the report from that. Period.

Maxim 19: Long Beats Wide.

Re: PROC Report Problem with column name

Gieorgie — Sat, 30 Oct 2021 07:47:00 GMT

Ok, I understand so I planned from the beginning and I wanted to do so. However, is there any solution to adding a column next to the column rather than a row below the row for each proc append run

Re: PROC Report Problem with column name

Kurt_Bremser — Sat, 30 Oct 2021 07:50:20 GMT

PROC REPORT and PROC TRANSPOSE do this automatically, but your working dataset MUST be long.

Re: PROC Report Problem with column name

Gieorgie — Sat, 30 Oct 2021 08:13:34 GMT

So after create tables go proc transpose data= (policy_vintage_weekly) out=long1;
by Run_Date:
By Policy_vintage
run; and after that append to historical file and next to create some reports?

Re: PROC Report Problem with column name

Kurt_Bremser — Sat, 30 Oct 2021 09:37:17 GMT

NO!!!

You keep ALL datasets in the long layout, append that, and do any transposing (either with TRANSPOSE or REPORT) as a FINAL(!!!) step.

Re: PROC Report Problem with column name

Gieorgie — Sat, 30 Oct 2021 11:17:44 GMT

Kurt sorry but I'm still confuse. My point is how to set proc append or proc transpose so that the wlong setting does not add data as consecutive rows or columns. There is a historical file that starts with Run_Date from 1, then if I run tomorrow with number 2. That number 2 should be the next column, not the next rows.

Re: PROC Report Problem with column name

Kurt_Bremser — Sat, 30 Oct 2021 11:39:34 GMT

I will say this a last time, in the hope that it finally sinks in:

ONE DOES NOT WORK WITH WIDE DATASETS!!!

Wide datasets are needed for regression (where you need a lot of predictors side-by-side with the outcome) or for presentation purposes. They are useless for work like joining, subsetting, grouping or appending (what you experience right now).

So you keep all your data in a long layout, can then simply append (as the structure is constant) and let SAS procedures do the work of transposing for you when needed for reporting.

Re: PROC Report Problem with column name

Gieorgie — Sat, 30 Oct 2021 11:43:06 GMT

Ok now i get it, sorry i hope i didn't ruin your weekend 🙂