PROC Report Problem with column name

[Gieorgie]

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.

[ballardw]

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

[Gieorgie]

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.

[ballardw]

---

@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.

[Gieorgie]

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

[Kurt_Bremser]

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

[Kurt_Bremser]

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.

[Gieorgie]

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

[Kurt_Bremser]

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).

[Gieorgie]

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?

[Gieorgie]

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?

[Kurt_Bremser]

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.

[Gieorgie]

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

[Kurt_Bremser]

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

[Gieorgie]

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?