Solved

# change column data to rows

Posted on 2011-09-21
716 Views
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
Question by:sam2929
• 5
• 4

LVL 11

Expert Comment

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 Comment

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

LVL 11

Expert Comment

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

LVL 11

Expert Comment

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 Comment

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

LVL 11

Accepted Solution

theartfuldazzler earned 500 total points
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 Comment

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

LVL 11

Expert Comment

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

LVL 2

Expert Comment

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 Comment

looking in to it
0