DATA Step, Macro, Functions and more

Transpose - Vertical Dataset

Accepted Solution Solved
Reply
Contributor
Posts: 52
Accepted Solution

Transpose - Vertical Dataset

How do I transpose “WANT” to view as SAMPLE A below?

Keep in mind the list of variables aren't just defined to:

ID

Crayon

PayFine

Fruit & ChargeTax.

There are over 60 variables.

DATA A:

length name  $ 30 codes $ 10 codevalues $50 ;

input NAME CODES CODEVALUES ;

cards;

Crayon 01 Blue

Crayon 02 Red

Crayon 03 Yellow

PayFine . .

Fruit 01 Apple

Fruit 02 Watermelon

ChargeTax 01 Yes

ChargeTax 02 No

;

run;

DATA B;

  input (ID  Crayon PayFine  Fruit  ChargeTax ) ( : $40.);

cards;

12345 01 Yes  02 01

67891 03 No  02 02

00001 02 . 01 01

;

run;

data want;

if _n_ eq 1 then do;

  if 0 then set a;

  declare hash h(dataset:'a');

h.definekey('name','codes');

h.definedata('codevalues');

   h.definedone();

end;

set b;

array x{*} $ Crayon--ChargeTax ;

do i=1 to dim(x);

name=vname(x{i});codes=x{i};

rc=h.find();

if rc=0 then x{i}=coalescec(codevalues,x{i});

end;

drop  NAME CODES CODEVALUES rc i;

run;

WORK.WANT

ID

Crayon

PayFine

Fruit

ChargeTax

12345

Blue

Yes

Watermelon

Yes

67891

Yellow

No

Watermelon

No

00001

Red

Apple

Yes

SAMPLE A:

ID

ID

ID

12345

67891

00001

Crayon

Crayon

Crayon

Blue

Yellow

Red

PayFine

PayFine

PayFine

Yes

No

Fruit

Fruit

Fruit

Watermelon

Watermelon

Apple

ChargeTax

ChargeTax

ChargeTax

Yes

No

Yes


Accepted Solutions
Solution
‎07-14-2014 06:17 PM
Super User
Super User
Posts: 6,502

Re: Transpose - Vertical Dataset

I have to agree that the desired output is very strange looking, but here is one method.

Basically transpose the data and then read each record from the transposed data twice. On the first one convert all of the values to the name of the variable that the row contains.

data have ;

  input (ID Crayon PayFine Fruit ChargeTax) (:$20.);

cards;

12345 Blue Yes Watermelon Yes

67891 Yellow No Watermelon No

00001 Red Apple Yes .

;;;;

proc transpose data=have out=intermediate ;

  var _all_;

run;

data want ;

  set intermediate ;

  array c _all_;

  do over c; c=_name_; end;

  output;

  set intermediate;

  output;

  drop _name_;

run;

proc print;

run;

View solution in original post


All Replies
Valued Guide
Posts: 3,208

Re: Transpose - Vertical Dataset

Review the using of formats these are table lookups alike using in memory translation.

This is by far the quickest .method and it is possible to organize your support conforming version and release management requirements

---->-- ja karman --<-----
Contributor
Posts: 52

Re: Transpose - Vertical Dataset

Jaap,

Can you please elaborate?

Thanks...

Super User
Posts: 17,963

Re: Transpose - Vertical Dataset

Just to clarify, your Sample A is what you actually want, and WANT is what you have?

If so, the requirement of SAMPLE A seems horribly inefficient to me, why would you want it stored that way?

Solution
‎07-14-2014 06:17 PM
Super User
Super User
Posts: 6,502

Re: Transpose - Vertical Dataset

I have to agree that the desired output is very strange looking, but here is one method.

Basically transpose the data and then read each record from the transposed data twice. On the first one convert all of the values to the name of the variable that the row contains.

data have ;

  input (ID Crayon PayFine Fruit ChargeTax) (:$20.);

cards;

12345 Blue Yes Watermelon Yes

67891 Yellow No Watermelon No

00001 Red Apple Yes .

;;;;

proc transpose data=have out=intermediate ;

  var _all_;

run;

data want ;

  set intermediate ;

  array c _all_;

  do over c; c=_name_; end;

  output;

  set intermediate;

  output;

  drop _name_;

run;

proc print;

run;

Valued Guide
Posts: 3,208

Re: Transpose - Vertical Dataset

Dsrountree, Your first question is this one: https://communities.sas.com/thread/59448 .  At the end a little explanation is done. The A table(s) are fixe coming in (government regulation) Regulations & Guidance - Centers for Medicare & Medicaid Services and the B is probably your local data. You are also saying being new to SAS. What I see you are doing is not recognizing the SAS features helping you but are progamminng in eg JAVA and code that into SAS.  I have seen the Cobol programmers doing SAS. 

What are the differences with SAS, just the major ones, to other languages:

- The data processing with SAS is not having the row dimension of processing  data in a tabular format. This is like SQL or RPG but quite different to Cobol/Java.

  The differences with SQL is that the datastep is using a PDV (Program Data Vector) where the data is being ordered in a fixed way.  (Lag / retain)

- Having standard procedures/packages for many types. This is the important reporting and stat part.  

- code parsing is done as an interpreter with some boundaries.

- (much more)    

- Formats This concept is old and still rather unique. It will transform a real internal value to something different.

  The functionality can be seen as a table/hash lookup (one way to use it) but is more a module being called before the internal value is presented.

  The excel picture and some (recode translate) in other languages are coming close to what a sas format is doing.

SAS is having an internal solution to standardize, translate, internal values to something defined different.

Having seen your question recognizing this, why should you try to invent an own solution of something that is present?

For each type of translation you can define a format (presentation output) / informat (input) doing that for you.

During processing of the data formats will be get loaded into memory and all processing is done in memory.

Maintaining formats (your predefined standard translations) can be done be isolating them in dedicated libraries (single version/release).

The SAS(R) 9.4 System Options: Reference, Second Edition (fmtsearch) is the SAS system option that will make your librarie available.
Defining a translation is nothing more with SAS as associating the format. That is all the coding needed at the data-processing moment.

No hash joins of other programming blocks. Very simple as it almost nothing. Why overcomplicating the solution?      

---->-- ja karman --<-----
Contributor
Posts: 52

Re: Transpose - Vertical Dataset

Thanks for all your help and input...

I agree with the comment in regards to the layout but upper mgt wants it layed out the same way you would see specs on different model cars (top down).

CAR A          CAR B          CAR C

32 MPG        29 MPG        40MPG

2 DOOR        2 DOOR        4 DOOR

300 HP         450 HP          250 HP

During one of my many data pulls the data would never be presented in this fashion but when mgt is looking to compare several competitive plans this is the layout they request.

In the past it was done manually by another analyst who couldn't transpose the data correctly.

Again Thanks - I love the feedback I get - trust me it helps alot.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 290 views
  • 5 likes
  • 4 in conversation