topic Re: Main query bombs because tranpose output varies in SAS Enterprise Guide

Main query bombs because tranpose output varies

JSJ — Fri, 11 Sep 2015 19:30:42 GMT

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?

Re: Main query bombs because tranpose output varies

Fugue — Fri, 11 Sep 2015 19:44:32 GMT

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.

Re: Main query bombs because tranpose output varies

JSJ — Fri, 11 Sep 2015 19:47:32 GMT

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

Not sure!

Any help is appreciated. Thanks

Re: Main query bombs because tranpose output varies

Fugue — Fri, 11 Sep 2015 23:16:02 GMT

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

Re: Main query bombs because tranpose output varies

Patrick — Sat, 12 Sep 2015 04:46:33 GMT

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;

Re: Main query bombs because tranpose output varies

Tom — Sat, 12 Sep 2015 11:30:36 GMT

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;