• Status: Solved
• Priority: Medium
• Security: Public
• Views: 801

change column data to rows

we have table which looks like

id          column_all

1001         ZC000Z01
1001         ZC000Z02

1002         00Z01
1002         00Z0Z02

we want  to convert row to columns so if we look at column_all and last three digit of data
is 'Z01' OR 'Z02' then put that data in column_Z01   Column_Z02

id  column_Z01   Column_Z02
1001  ZC000Z01     ZC000Z02
1002  00Z01        00Z0Z02
0
sam2929
• 5
• 4
1 Solution

Commented:
Hi

See the attached code:

``````DATA Indata;
infile cards truncover DLM='|';
informat id best10. column_all \$20.;
input id column_all;
cards;
1001|ZC000Z01
1001|ZC000Z02
1002|00Z01
1002|00Z0Z02
;
RUN;

PROC SQL;
create table in2 as
select *, 'Column_'||substr(column_all, length(column_all)-2) as ZCol
from indata
order by id;

proc transpose data=in2 out=out1(drop=_NAME_);
by id;
id Zcol;
var  column_all;
RUN;
``````

Note that this assumes that in the table "in2" there are no duplicates of the combination (id and ZCol)

Dazz
0

Author Commented:
we have lots of data so we just can't hardcode this variables we need to figure out to just get
last three digits and then transpose
can't be hardcoded
1001|ZC000Z01
1001|ZC000Z02
1002|00Z01
1002|00Z0Z02
0

Commented:
Hi Sam

That is what the code does...

'Column_'||substr(column_all, length(column_all)-2)

gives a distinct column name with 'Column_' added onto the last three digits of 'column_all' variable.

Try it on your data, and if there are still issues, send us part of the log...

0

Commented:
Hi

Apologies - I think I understand your issue now...

I just included the DATA INDATA step to get a sample data set.    Run the code on your data from the PROC SQL statement, changing "Indata" to your data set name.  It should work from there...

D

0

Author Commented:
we just want to bring records for 'Z01' OR 'Z02'  and not all records so how can i modify  this below sql

PROC SQL;
create table in2 as
select *, 'Column_'||substr(column_all, length(column_all)-2) as ZCol
from indata
order by id;

proc transpose data=in2 out=out1(drop=_NAME_);
by id;
id Zcol;
var  column_all;
RUN;
0

Commented:
Hi

oh ok...  Just add a WHERE clause:

PROC SQL;
create table in2 as
select *, 'Column_'||substr(column_all, length(column_all)-2) as ZCol
from indata
where substr(column_all, length(column_all)-2) in ('Z01','Z02')
order by id;

proc transpose data=in2 out=out1(drop=_NAME_);
by id;
id Zcol;
var  column_all;
RUN;

0

Author Commented:
Hi,
I am getting error below proc sql works fine i am using di studio sql and for transpose
i am getting error sending code as attachment

so in this case

proc transpose data=in2 out=out1(drop=_NAME_);
by id will be  CRM_OBJ_ID;
id Zcol will be TRANSPOSE_COLUMN;
var  column_all will be PARTNER_ID"n;
RUN;

ERROR: The ID value "Column_009" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
Transaction Number=0100001759
ERROR: Data set WORK.W61QV6G1 is not sorted in ascending sequence. The current BY group has Transact
ion Number = 0100009616 and the
next BY group has Transaction Number = 0100008016.

``````%let _INPUT_count = 1;
%let _INPUT = work."W61QV6G1"n;
%let _INPUT_connect = ;
%let _INPUT_engine = ;
%let _INPUT_memtype = VIEW;
%let _INPUT_options = %nrquote();
%let _INPUT_alter = %nrquote();
%let _INPUT_path = /SQL Join_A5LC1DEC.AH0000BD(WorkTable);
%let _INPUT_type = 1;
%let _INPUT_label = %nrquote();

%let _INPUT1 = work."W61QV6G1"n;
%let _INPUT1_connect = ;
%let _INPUT1_engine = ;
%let _INPUT1_memtype = VIEW;
%let _INPUT1_options = %nrquote();
%let _INPUT1_alter = %nrquote();
%let _INPUT1_path = /SQL Join_A5LC1DEC.AH0000BD(WorkTable);
%let _INPUT1_type = 1;
%let _INPUT1_label = %nrquote();

%let _OUTPUT_count = 1;
%let _OUTPUT = work."W61R7VNE"n;
%let _OUTPUT_connect = ;
%let _OUTPUT_engine = ;
%let _OUTPUT_memtype = DATA;
%let _OUTPUT_options = %nrquote();
%let _OUTPUT_alter = %nrquote();
%let _OUTPUT_path = /Transpose_OUTPUT_A5LC1DEC.\$0000146(WorkTable);
%let _OUTPUT_type = 1;
%let _OUTPUT_label = %nrquote();
/* List of target columns to keep  */
%let _OUTPUT_keep = "CRM_OBJ_ID"n "PARTNER_ID"n "CRM_OHGUID"n "BIC_ZI_CRETSP"n
"PARTNER_CITY"n "PARTNER_REGN"n "PARTNER_CNTRY"n "TRANSPOSE_COLUMN"n;

proc datasets lib=work nolist nowarn memtype = (data view);
delete "W61R7VNE"n;
quit;

%let varColumns = "PARTNER_ID"n;
%let varColumns_count = 1;
%let varColumns0 = 1;
%let varColumns1 = "PARTNER_ID"n;
%let copyColumns = ;
%let copyColumns_count = 0;
%let copyColumns0 = 0;
%let IDColumn = "TRANSPOSE_COLUMN"n;
%let IDColumn_count = 1;
%let IDColumn0 = 1;
%let IDColumn1 = "TRANSPOSE_COLUMN"n;
%let IDLabelColumn = ;
%let IDLabelColumn_count = 0;
%let IDLabelColumn0 = 0;
%let byColumns = "CRM_OBJ_ID"n;
%let byColumns_count = 1;
%let byColumns0 = 1;
%let byColumns1 = "CRM_OBJ_ID"n;
%let Options = ;
%let procOptions = ;

/* List of target columns to keep  */
%let _keep = "CRM_OBJ_ID"n "PARTNER_ID"n "CRM_OHGUID"n "BIC_ZI_CRETSP"n
"PARTNER_CITY"n "PARTNER_REGN"n "PARTNER_CNTRY"n "TRANSPOSE_COLUMN"n;
/* List of target columns to keep  */
%let keep = "CRM_OBJ_ID"n "PARTNER_ID"n "CRM_OHGUID"n "BIC_ZI_CRETSP"n
"PARTNER_CITY"n "PARTNER_REGN"n "PARTNER_CNTRY"n "TRANSPOSE_COLUMN"n;
%macro Transpose;

options mprint;

%if (%quote(&options) ne) %then
%do;
options &options;
%end;

proc transpose data = &_INPUT (&_INPUT_options)
out = &_OUTPUT (&_OUTPUT_options) &procOptions;

%if (%quote(&byColumns) ne) %then
%do;
by &byColumns;
%end;

%if (%quote(&varColumns) ne) %then
%do;
var &varColumns;
%end;

%if (%quote(&copyColumns) ne) %then
%do;
copy &copyColumns;
%end;

%if (%quote(&idColumn) ne) %then
%do;
id &idColumn;

%if (%quote(&idlabelColumn) ne) %then
%do;
idlabel &idlabelColumn;
%end;

%end;

run;
%RCSET(&syserr);
%mend Transpose;

%Transpose;

%rcSet(&syserr);
%rcSet(&sysrc);
%rcSet(&sqlrc);
``````
0

Commented:
Hi

1)
ERROR: Data set WORK.W61QV6G1 is not sorted in ascending sequence. The current BY group has Transaction Number = 0100009616 and the
next BY group has Transaction Number = 0100008016.

The ORDER statement in the PROC SQL should solve this ("Order by id" )
Alternatively - use PROC SORT:

PROC SORT DATA=In2;
BY Id;
RUN;

2)
ERROR: The ID value "Column_009" occurs twice in the same BY group.
NOTE: The above message was for the following BY group:
Transaction Number=0100001759

This essentially means that for the id = 0100001759, there are two values for Column_009.

For instance:

_N_=1 ID= 0100001759 Column_all= 1234009 ZCol=Column_009
_N_=2 ID= 0100001759 Column_all= ABCD009 ZCol=Column_009

SAS is now unsure which value to put in Column_009 - either 1234009 or ABCD009?  I am not sure what you want your output to look like?  The quick and easy way to get around this, is to let your TRANSPOSE options = 'LET';  (ie: in the code: %let procOptions = LET;)  In this case, SAS will pick the last Column_all value to put into the table.

regards

0

Commented:
Hello sam2929,

This one looked like fun. So here you go. This might be a solution.

``````/*options mprint macrogen mlogic symbolgen;*/

/* Build the macro variables used in the process */
Proc SQL NOPRINT;

SELECT DISTINCT
'column_' || substr( Column_all, Length(Column_all) -2, 3) || ' \$10.'
INTO :column_all separated by ' '
FROM work.tall;

SELECT DISTINCT
'column_' || substr( Column_all, Length(Column_all) -2, 3)
INTO :cols_sas separated by ' '
FROM work.tall;

SELECT DISTINCT
'column_' || substr( Column_all, Length(Column_all) -2, 3) || " = .; "
INTO :cols_clear separated by ' '
FROM work.tall;

SELECT DISTINCT "If substr( Column_all, Length(Column_all) -2, 3) = '" ||
substr( Column_all, Length(Column_all) -2, 3) ||
"' Then column_" || substr( Column_all, Length(Column_all) -2, 3) ||
" = column_all"
INTO :column_case separated by '; '
FROM work.tall;

QUIT;

proc sort data=tall out=tall_sort;

by id column_all;

run;

Data wide2( drop=column_all id rename=(id_hold=id) );

length id_hold 8. &column_all.;
retain id_hold &cols_sas.;

set tall_sort end=lastid;

/* First observation considerations */
If _n_ = 1 then do;

id_hold = id;

end;

/* Id change logic */
If id ne id_hold then do;

output;
&cols_clear.;
id_hold = id;

end;

/* Conditionally load the data columns */
&column_case.;

/* Last Record consideration */
If lastid then do;

output;

End;

run;
``````
EE-test-data.sas.txt
0

Author Commented:
looking in to it
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.