BookmarkSubscribeRSS Feed
JSJ
Quartz | Level 8 JSJ
Quartz | Level 8

We have been happily using the transpose in SAS Eg to collapse lines of data - or flatten data in adhoc reports.

 

ID

Gift

     

1

$50

     

1

$40

     

1

$30

     

1

$20

     

2

$50

     

2

$40

     
         

ID

Gift 1

Gift 2

Gift 3

Gift 4

1

$50

$40

$30

$20

2

$50

$40

 

 

 

This works great for adhoc reports.  But then, someone asks us to add it to the portal.(Listing of pre-written reports).

The problem is that the number of columns, in this case Gift 1, Gift 2 can change (either go up or down) depending on several things.  Let's say they are prompted with fiscal year.

If the original load had Gift 1 - Gift 4, but then say we prompt for the gift year and the data from that year has less data.  For example, the  transpose only produces Gift 1- Gift 3  , then our "Main" report will bomb because Gift 4 will not be defined.

Do you know of a way to get around this?
 
5 REPLIES 5
Fugue
Quartz | Level 8
A relatively straightforward solution is to ensure that the final dataset you are transposing has all possible levels of the variable, by merging them in prior to the transpose.
JSJ
Quartz | Level 8 JSJ
Quartz | Level 8

How can you force in null values into variables that don't exist in SAS EG?

Not sure!

Any help is appreciated. Thanks

Jo

Fugue
Quartz | Level 8

Here's a sample that I've adapted from SAS support examples online. It uses PROC TABULATE with the PRELOADFMT and PRINTMISS options and OUT= to create a table with all possible combinations of class variables. I'm sure you'll get the idea from this code sample.

 

(In your specific case, the FORMAT you should create would be for fiscal year. Obviously, fiscal year isn't in this example. It's just to illustrate how you can solve the problem of having missing classification levels. There are other approaches that could also be used . . . .)

 

data energy;

   length State $2;

   input Region Division state $ Type Expenditures;

   datalines;

1 1 ME 1 708

1 1 ME 2 379

1 1 NH 1 597

1 1 NH 2 301

1 1 VT 1 353

1 1 VT 2 188

1 1 MA 1 3264

1 1 MA 2 2498

1 1 RI 1 531

1 1 RI 2 358

1 1 CT 1 2024

1 1 CT 2 1405

1 2 NY 1 8786

1 2 NY 2 7825

1 2 NJ 1 4115

1 2 NJ 2 3558

1 2 PA 1 6478

1 2 PA 2 3695

4 3 MT 1 322

4 3 MT 2 232

4 3 ID 1 392

4 3 ID 2 298

4 3 WY 1 194

4 3 WY 2 184

4 3 CO 1 1215

4 3 CO 2 1173

4 3 NM 1 545

4 3 NM 2 578

4 3 AZ 1 1694

4 3 AZ 2 1448

4 3 UT 1 621

4 3 UT 2 438

4 3 NV 1 493

4 3 NV 2 378

4 4 WA 1 1680

4 4 WA 2 1122

4 4 OR 1 1014

4 4 OR 2 756

4 4 CA 1 10643

4 4 CA 2 10114

4 4 AK 1 349

4 4 AK 2 329

4 4 HI 1 273

4 4 HI 2 298

;

;;;;

 

proc format;

   value regfmt 1='Northeast'

                2='South'

                3='Midwest'

                4='West';

   value divfmt 1='New England'

                2='Middle Atlantic'

                3='Mountain'

                4='Pacific';

   value usetype 1='Residential Customers'

                 2='Business Customers';

run;

 

proc tabulate data=energy format=dollar12. out=outtab;

   class region division type / preloadfmt;

   var expenditures;

   table region*division,

         type*expenditures / rts=25 printmiss;

   format region regfmt. division divfmt. type usetype.;

   title 'Energy Expenditures for Each Region';

   title2 '(millions of dollars)';

run;

 

. . . . then proc transpose outtab . . . . 

Patrick
Opal | Level 21

If you want full control over the final data set structure used for the report then something like below should work:

 

data have;
infile datalines truncover dlm='$';
input ID Gift;
format Gift dollar10.;
datalines;
1$50
1$40
1$30
1$20
2$50
2$40
;
run;

proc transpose data=have out=transp(drop=_: ) prefix=Gift;
var gift;
by id;
run;

data want;
attrib
id length=8
Gift1-Gift6 length=8 format=dollar10.;
stop;
run;

proc append base=want data=transp nowarn force;
run;

Tom
Super User Tom
Super User

One way is to write you own transpose step instead of using the EG template.  Then you can define the number of "GIFT" columns that are defined.  So for example this code will create GIFT1 to GIFT4 and than any other gifts will be summarized in GIFT_REST.

 

data want ;
  set have ;
  by ID ;
  array G gift1-gift4 gift_rest ;
  if first.id then do ;
    call missing(of G(*));
    col=0;
  end;
  col+1;
  if last.id then col=5 ;
  g(col) = sum(g(col),GIFT);
  if last.id then output;
  retain gift1-gift4 gift_rest ;
  keep id gift1-gift4 gift_rest ;
run;

 

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 5 replies
  • 1393 views
  • 0 likes
  • 4 in conversation