Transpose in SAS EG bringing only one row of data

gregor1 — Fri, 21 Aug 2020 17:00:59 GMT

I brought data in from an API to SAS which gave me the following output:

P P1 P2 V Value
1 result 0
2 result c_number 1 123
2 result cp_policy.contract_state_code 1 OH
2 result cref_referred_date 1 2019-09-23
2 result cref_response_date 1 2019-09-27
2 result c_case_open_timestamp 1 2019-09-23
1 result 0
2 result c_number 1 456
2 result cp_policy.contract_state_code 1 MO
2 result cref_referred_date 1 2020-01-24
2 result cref_response_date 1 2020-02-04
2 result c_case_open_timestamp 1 2020-01-24
1 result 0
2 result c_number 1 789
2 result cp_policy.contract_state_code 1 MN
2 result cref_referred_date 1 2020-03-19
2 result cref_response_date 1 2020-03-19
2 result c_case_open_timestamp 1 2020-03-13

Now I want to transpose so I get the following:

c_number cp_policy.contract_state_code cref_referred_date cref_response_date c_case_open123 OH 2019-09-23 2019-09-27 2019-09-23

456 MO 2020-01-24 2020-02-04 2020-01-24

789 MN 2020-03-19 2020-03-19 2020-03-13

SAS EG wrote the following code, but I only get the last row of data to come over (i.e., 789 MN 2020-03-19 2020-03-19 2020-03-13):

/* -------------------------------------------------------------------
Code generated by SAS Task

Generated on: Friday, August 21, 2020 at 11:54:11 AM
By task: Transpose

Input Data: SASApp:WORK.OUT
Server: SASApp
------------------------------------------------------------------- */

%_eg_conditional_dropds(WORK.TRNSTransposed,
WORK.SORTTempTableSorted);
/* -------------------------------------------------------------------
Sort data set SASApp:WORK.OUT
------------------------------------------------------------------- */

PROC SQL;
CREATE VIEW WORK.SORTTempTableSorted AS
SELECT T.Value
FROM WORK.OUT as T
;
QUIT;
PROC TRANSPOSE DATA=WORK.SORTTempTableSorted
OUT=WORK.TRNSTransposed(LABEL="Transposed WORK.OUT")
PREFIX=Column
LET
NAME=Source
LABEL=Label
;
VAR Value;

/* -------------------------------------------------------------------
End of task code
------------------------------------------------------------------- */
RUN; QUIT;
%_eg_conditional_dropds(WORK.SORTTempTableSorted);
TITLE; FOOTNOTE;

Any ideas on how I should edit the code to bring over all rows of data instead of just the last line?

Re: Transpose in SAS EG bringing only one row of data

Reeza — Fri, 21 Aug 2020 17:02:59 GMT

Your sort/select looks possibly incorrect, It only selects a single column "VALUE"?

Re: Transpose in SAS EG bringing only one row of data

ballardw — Fri, 21 Aug 2020 21:16:03 GMT

You need a variable, or combination of variables that identify each "group" of related records. Which I don't see, that would create the output data that you want.

So your first step may be to add such a grouping variable.

Sort your data by that variable.

Then you transpose would use that variable as a BY variable so Proc Transpose knows to group things.

The P2 variable would be used as an ID variable to create the names of the output variables as shown in the desired output.

You apparently may have indicated some incorrect options to subset your data to only include "value" for the transpose.

Re: Transpose in SAS EG bringing only one row of data

gregor1 — Fri, 21 Aug 2020 21:03:03 GMT

Thanks Reeza!

Re: Transpose in SAS EG bringing only one row of data

gregor1 — Fri, 21 Aug 2020 21:04:08 GMT

Thanks BallardW - this helped and I got it to work.

topic Re: Transpose in SAS EG bringing only one row of data in SAS Enterprise Guide

Transpose in SAS EG bringing only one row of data

Re: Transpose in SAS EG bringing only one row of data

Re: Transpose in SAS EG bringing only one row of data

Re: Transpose in SAS EG bringing only one row of data

Re: Transpose in SAS EG bringing only one row of data