Help using Base SAS procedures

transpose data by re-arranging data in columns (?)

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

transpose data by re-arranging data in columns (?)

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.


Accepted Solutions
Solution
‎10-29-2014 11:48 PM
Respected Advisor
Posts: 3,799

Re: transpose data by re-arranging data in columns (?)

Posted in reply to erickbernard

"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


All Replies
Respected Advisor
Posts: 4,919

Re: transpose data by re-arranging data in columns (?)

Posted in reply to erickbernard

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
Solution
‎10-29-2014 11:48 PM
Respected Advisor
Posts: 3,799

Re: transpose data by re-arranging data in columns (?)

Posted in reply to erickbernard

"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
Occasional Contributor
Posts: 11

Re: transpose data by re-arranging data in columns (?)

Posted in reply to data_null__

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.

Contributor
Posts: 45

Re: transpose data by re-arranging data in columns (?)

Posted in reply to erickbernard

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;

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 242 views
  • 3 likes
  • 4 in conversation