Transpose in SAS EG bringing only one row of data

[gregor1]

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?        

[Reeza]

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

[gregor1]

Thanks Reeza!

[ballardw · Accepted solution]

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.

[gregor1]

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