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 |
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;
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
Jaap,
Can you please elaborate?
Thanks...
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?
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;
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?
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.