change column data to rows

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;

Select allOpen in new window

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

Dazz

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

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

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

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;

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);

Select allOpen in new window

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

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;

Select allOpen in new window

EE-test-data.sas.txt

looking in to it