With monday.comâ€™s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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

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

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

last three digits and then transpose

can't be hardcoded

1001|ZC000Z01

1001|ZC000Z02

1002|00Z01

1002|00Z0Z02

That is what the code does...

'Column_'||substr(column_a

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...

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

PROC SQL;

create table in2 as

select *, 'Column_'||substr(column_a

from indata

order by id;

proc transpose data=in2 out=out1(drop=_NAME_);

by id;

id Zcol;

var column_all;

RUN;

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
"BIC_ZI_CHGTSP"n "PARTNER_NME"n "PARTNER_PC"n "PARTNER_ADDR"n "Runtime"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
"BIC_ZI_CHGTSP"n "PARTNER_NME"n "PARTNER_PC"n "PARTNER_ADDR"n "Runtime"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
"BIC_ZI_CHGTSP"n "PARTNER_NME"n "PARTNER_PC"n "PARTNER_ADDR"n "Runtime"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(©Columns) ne) %then
%do;
copy ©Columns;
%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);
```

There are 2 errors, lets start with the easy one:

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

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
All Courses

From novice to tech pro — start learning today.

oh ok... Just add a WHERE clause:

PROC SQL;

create table in2 as

select *, 'Column_'||substr(column_a

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;