Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

change column data to rows

Posted on 2011-09-21
10
Medium Priority
?
774 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

604 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