BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
erickbernard
Calcite | Level 5

Hi there,

Here I am again with my neverending transposition questions. Though, I'm not sure if this is necesarily a transposition problem.

Here's what I have;

item1item2
CompanyABCorganisation
domestic

location

cashtype
123assets
456liabilities

and this is what I want;

organisationitem_namevalue
CompanyABCdomestic || cash || assets123
CompanyABCdomestic || cash || liabilities456

and here's a dummy code;

data have;

infile cards dsd;

input (item1-item2) (:$16.);

cards;

CompanyABC,organisation

domestic,location

cash,type

123,assets

456,liabilities

;

run;

If you excuse me, I'm taking a break from this for the next 30 minutes and will have a cup of coffee.

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

"It's déjà vu all over again."



data have;
   input (item1 item2)(:$16.);
cards;
CompanyABC organisation
domestic location
cash type
123 assets
456 liabilities
;;;;
   run;
proc transpose out=have2;
   var item1;
   id item2;
   run;
proc print;
  
run;
proc transpose out=have3 name=item prefix=value;
   by organisation location type;
   var assets liabilities;
   run;
proc print;
  
run;

10-29-2014 10-42-19 PM.png

View solution in original post

4 REPLIES 4
PGStats
Opal | Level 21

This should do:

data want;

set have;

select (item2);

    when ("location") location = item1;

    when ("type") type = item1;

    when ("organisation") do;

        organisation = item1;

        call missing(location, type);

        end;

    otherwise do;

        item_name = catx(" || ", location, type, item2);

        value = item1;

        output;

        end;

    end;

retain organisation location type;

keep organisation item_name value;

run;

PG

PG
data_null__
Jade | Level 19

"It's déjà vu all over again."



data have;
   input (item1 item2)(:$16.);
cards;
CompanyABC organisation
domestic location
cash type
123 assets
456 liabilities
;;;;
   run;
proc transpose out=have2;
   var item1;
   id item2;
   run;
proc print;
  
run;
proc transpose out=have3 name=item prefix=value;
   by organisation location type;
   var assets liabilities;
   run;
proc print;
  
run;

10-29-2014 10-42-19 PM.png
erickbernard
Calcite | Level 5

thank you data_null; that's exactly what I needed. One of these days, I need to do a cheat sheet for transposing data because I'm having a lot of difficulty visualiising it  in my mind and as a result coming empty handed. Thank you again.

RaviKommuri
Fluorite | Level 6

Another way......(may not be optimized code)

DATA WANT(KEEP=ORG ITEM_NAME VALUE);

SET HAVE;

LENGTH ORG $16. ITEM_NAME $40. VALUE 3.;

IF UPCASE(ITEM2) = 'ORGANISATION' THEN ORG = ITEM1;

IF UPCASE(ITEM2) = 'LOCATION' THEN LOCATION = ITEM1;

IF UPCASE(ITEM2) = 'TYPE' THEN TYPE = ITEM1;

IF UPCASE(ITEM2) = 'ASSETS' OR UPCASE(ITEM2) = 'LIABILITIES' THEN MOD = ITEM2; 

IF UPCASE(ITEM2) = 'ASSETS' OR UPCASE(ITEM2) = 'LIABILITIES' THEN VALUE = ITEM1;

RETAIN ORG LOCATION TYPE;

IF VALUE = . THEN DELETE;

ITEM_NAME = LOCATION||TYPE||MOD;

RUN;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 4 replies
  • 872 views
  • 3 likes
  • 4 in conversation