Solved

change column data to rows

Posted on 2011-09-21
10
716 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
  • 5
  • 4
10 Comments
 
LVL 11

Expert Comment

by:theartfuldazzler
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 11

Expert Comment

by:theartfuldazzler
Comment Utility
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
Comment Utility
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 11

Accepted Solution

by:
theartfuldazzler earned 500 total points
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
looking in to it
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now