- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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?
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks BallardW - this helped and I got it to work.