BookmarkSubscribeRSS Feed
helloSAS
Obsidian | Level 7

Hi,

I'm trying to generate a report. Can you please help me to perform the below task?

My data set has ony 2 row and multiple variables.

Ex

var1     var2     var3     var4     var5     var6     var7

345     745     456        .          .         .         109

  .        .           .        546      567     678      220

    

    

I want my report to look like this

var1     345

var2     745

var3     456

var4     546

var5     567

var6     578

var7     329  (109 + 220)

11 REPLIES 11
Tom
Super User Tom
Super User

proc summary data=have ;

   var _numeric_;

   output out=summary(drop=_:)  sum= ;

run;

proc transpose data=summary out=want ;

   var _numeric_;

run;

proc print data=want ;

   var _name_ col1 ;

run;

Try it with SASHELP.CLASS as the input to the first step and you will get this output.

Obs    _NAME_     COL1

1     Age        253.0

2     Height    1184.4

3     Weight    1900.5

art297
Opal | Level 21

data have;

  input var1-var7;

  cards;

345     745     456        .          .         .         109

  .        .           .        546      567     678      220

;

proc transpose data=have out=want;

run;

data want (drop=col:);

  set want;

  length value $20;

  value=strip(put(sum(of col:),best12.));

  if n(of col:) gt 1 then do;

    value=catt(value,' (',col1,' + ',col2,')');

  end;

run;

ods pdf file="c:\art\testpdf.pdf";

proc print;

run;

ods pdf close;

FriedEgg
SAS Employee

data foo;

input var1-var7;

cards;

345 745 456 .. . 109

. . . 546 567678 220

;

run;

proc transpose data=foo out=bar; run;

filename tmp temp;

ods pdf file=tmp notoc;

options nonumber nodate;

title 'My Report';

data _null_;

set bar;

Value=ifc(not nmiss(of col:),cat(sum(of col:),'(',col1,' + ',col2,')'),sum(ofcol:));

file print ods=(variables=(_name_ value));

put _ods_;

run;

My Report

NAME OF FORMER VARIABLEValue
var1345
var2745
var3456
var4546
var5567
var6678
var7329 (109 + 220)

The forum here adds all the crazy boarders...

helloSAS
Obsidian | Level 7

Thanks a lot all!

I got the info I need in my report but I need to improve it now  to make it look better and make more sense.

1. Is there a way I can arrange my observations in above report as I wish to?

Example:

I need to make my report look like this..

var1

var3

var8 = var1 - var3       /* I already created var8 */

/*Space here */

var2

var4

var9 = var2 - var4       /* I already created var9 */

/*Space here*/

etc..

2.

I tried using label to rename _NAME_ (that was created in transpose) in proc print ..it does not work.

Suggestions?

Thanks!

art297
Opal | Level 21

You have to be clearer regarding what you want.  According to what you posted, you don't indicate the variable label (if any) that you want to print and you don't include the values you said you needed.

Are the "/* I already created var8 */" comments strings that you want included in your report?

helloSAS
Obsidian | Level 7

My bad..I dont want the comment.

Var8 and var9 are diffence between var1, var3 and var2, var4. I want to show the differences in the report.

Also /*Space here */ means I want a space in my report after the first three lines.

1. Is there a way I can arrange my observations in above report as I wish to?

Example:

I need to make my report look like this..

var1  = 345

var3  = 456

var8 = -111

/*Space here */

var2 = 745

var4 = 546

var9 = 199      

/*Space here*/

art297
Opal | Level 21

Still not clear.  What determines the order of the variables, what data are you using, and what determines when you want to include a blank line?.  It is different than what you originally posted.

Tom
Super User Tom
Super User

To make that type of a report I would normally just use a PUT statement in a data step and not bother with transposing the data.

data _null_;

   set summary ;

   put (var1 var3 var8) (=/)

     / (var2 var4 var9) (=/)

   ;

run;

Tom
Super User Tom
Super User

Your original request had muliple components to it.

1) You want to sum the variables across observations.  You mentioned that you had two observations but the solution of using PROC SUMMARY will work for as many observations as your dataset has.

2) You wanted to output a report with the values rotated so that each variable was reported on another line.  This is what PROC TRANSPOSE was able to do.

You are now asking for some new things.

A) You want to create new variables based on arithmetic combinations of existing variables.  You could do that before step 1 or step 2 above.  For simple addition it doesn't matter. For other types of transformations it might.  Missing values might also impact your results. Here is a simple example:

    data a;  set b;

      var8 = var1 - var3 ;

      var9 = var2 - var4 ;

    run;

B) You appear perhaps to want to effect the order of the variables in the report?  For that you might want to replace the PROC TRANSPOSE used for step (2) above to a DATA step solution. This will give you more control over the order of the variables. For example you might want to do:

   data want ;

       set summary;

       array _n _numeric_;

      length varname $32 value 8;      

      do  i=1 to dim(_n);

            varname = vname(_n(i));

            value = _n(i);

            output;

       end;

      keep varname value;

   run;

To change the order replace _NUMERIC_ automatic variable list with an actual list of variable names in the order that you want them to appear.

C) Column header in the output? 

You can change the name that PROC TRANSPOSE uses for the _NAME_ variable by using dataset options where the output dataset name is specified.

proc transpose .... out=want( rename=(_name_=MyName)) ;

You could also just use a LABEL statement in the proc print.

proc print data=want label ;

   var _name_ col1 ;

   label _name_='Variable Name' col1='Variable Value';

run;

helloSAS
Obsidian | Level 7

The order of the variables will be standard and will not be determined by any logic. In my situation, I have various record counts and sums of variables from different files. Then I appended them into one data set. That is how I created those variables and their values that I posted in my original post.

The report I got from my original post was in one table. I would like to split it in multiple sections.

My report will always use var1 and var3 and its difference in 1st section. 2nd section will have var2 and var4 and its differnce.

Ksharp
Super User

Was it what you are looking for ?

data have;
  input var1-var7;
  cards;
345     745     456        .          .         .         109
  .        .           .        546      567     678      220
;
run;
proc summary data=have ;
   var var: ;
   output out=summary(drop=_:)  sum= ;
run;
proc transpose data=summary out=temp ;
   var var: ;
run;

data want(drop=name value);
 set temp;
 name=_name_;value=col1;
 if mod(_n_,3)=0 then do;
                        output;
                        call missing(_name_,col1);
                        output;
                       end;
  else output;
run;



Ksharp

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 1177 views
  • 2 likes
  • 5 in conversation