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

[erickbernard]

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;

item1	item2
CompanyABC	organisation
domestic	location
cash	type
123	assets
456	liabilities

and this is what I want;

organisation	item_name	value
CompanyABC	domestic || cash || assets	123
CompanyABC	domestic || cash || liabilities	456

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.

[PGStats]

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

[data_null__ · Accepted solution]

"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;

[erickbernard]

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]

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;