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
sam2929Asked:
Who is Participating?
 
theartfuldazzlerConnect With a Mentor 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
 
theartfuldazzlerCommented:
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;

Open in new window


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

Dazz
0
 
sam2929Author 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
The new generation of project management tools

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.

 
theartfuldazzlerCommented:
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
 
theartfuldazzlerCommented:
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
 
sam2929Author 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
 
sam2929Author 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 
        "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(&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);

Open in new window

0
 
theartfuldazzlerCommented:
Hi

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




0
 
sanofi-aventisCommented:
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;

Open in new window

EE-test-data.sas.txt
0
 
sam2929Author Commented:
looking in to it
0
All Courses

From novice to tech pro — start learning today.