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.
"It's déjà vu all over again."
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
"It's déjà vu all over again."
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.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.