Solved

change column data to rows

Posted on 2011-09-21
10
745 Views
Last Modified: 2013-11-16
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
Comment
Question by:sam2929
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 11

Expert Comment

by:theartfuldazzler
ID: 36578498
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
 

Author Comment

by:sam2929
ID: 36579163
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

by:theartfuldazzler
ID: 36579272
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 11

Expert Comment

by:theartfuldazzler
ID: 36579277
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

by:sam2929
ID: 36579477
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

by:
theartfuldazzler earned 500 total points
ID: 36579597
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

by:sam2929
ID: 36579654
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
 
LVL 11

Expert Comment

by:theartfuldazzler
ID: 36579788
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
 
LVL 2

Expert Comment

by:sanofi-aventis
ID: 36709157
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
 

Author Comment

by:sam2929
ID: 36951137
looking in to it
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Multiple MSSQL instances on same server 4 55
Question about DB Schema 27 70
MS SQL Server COnditional Where statement 7 97
mysql vs miscrosoft sql server 6 20
Creating and Managing Databases with phpMyAdmin in cPanel.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question