topic Re: SAS Programming in SAS Data Management

SAS DI Job: Create Predefined Target Table Structure Using Transposed Dataset

JJP1 — Wed, 28 Aug 2019 10:22:12 GMT

Hi All,

kindly find below sample input code.

Data t;
input NAME $5. 
ID  $3.
STATUS  $5.	
Reason_1 $4.
Reason_2 $4.	
Reason_3 $4.	
Reason_4 $4.	
Reason_5 $4.
Reason_6 $4.
Reason_7 $4.
Reason_8 $4.
Reason_9 $4.	
Reason_10 $4.	
Reason_11 $4.	
Reason_12 $4.	;
datalines;
44104 DD NA  AAG AAG AAG AAG AAG AAG AAG AAG AAG AAG AAG AAG AAG
8855     NA  AAG AAG AAG AAG AAG AAG AAG AAG AAG AAG AAG AAG AAG
26910    AC  TXN TXN TXN TXN TXN TXN TXN TXN TXN TXN TXN TXN TXN
8855     NA  TXN TXN TXN TXN TXN TXN TXN TXN TXN TXN TXN TXN TXN
44104 DD NA  TXN TXN TXN TXN TXN TXN TXN TXN TXN TXN TXN TXN TXN
44104 DD NA  BAN BAN BAN BAN BAN BAN BAN BAN BAN BAN BAN BAN BAN
8855     NA  CAH CAH CAH CAH CAH CAH CAH CAH CAH CAH CAH CAH CAH
8855     NA  FAL FAL FAL FAL FAL FAL FAL FAL FAL FAL FAL FAL FAL
26910    AC  RTM RTM RTM RTM RTM RTM RTM RTM RTM RTM RTM RTM RTM
8855     NA  UPD UPD UPD UPD UPD UPD UPD UPD UPD UPD UPD UPD UPD
44104 DD NA  SUC SUC SUC SUC SUC SUC SUC SUC SUC SUC SUC SUC SUC
26910    AC  SUC SUC SUC SUC SUC SUC SUC SUC SUC SUC SUC SUC SUC
;
run;

kindly find the below desired output that i need using above data please.

NAME

Status

Reason_1

Reason_2

Reason_3

Reason_4

Reason_5

Reason_6

Reason_7

Reason_8

Reason_9

Reason_10

Reason_11

Reason_12

44104

AAG

TXN

BAN

SUC

8855

AAG

TXN

CAH

FAL

UPD

26910

TXN

RTM

SUC

Re: SAS Programming

PeterClemmensen — Tue, 13 Aug 2019 10:57:22 GMT

Please explain the logic on how to get from your data to your desired output?

Re: SAS Programming

JJP1 — Tue, 13 Aug 2019 11:06:56 GMT

Thanks @PeterClemmensen .kindly find the attached sample input data and the output i need please

Re: SAS Programming

andreas_lds — Tue, 13 Aug 2019 11:17:29 GMT

@JJP1 wrote:

Thanks @PeterClemmensen .kindly find the attached sample input data and the output i need please

Please post data in usable form: a data step using datalines-statement and describe what should be done.

Re: SAS Programming

Ksharp — Tue, 13 Aug 2019 11:18:21 GMT

data have;
input x $;
cards;
Staus	ID	NAME	Reason_0	Reason_1	Reason_2	Reason_3	Reason_4	Reason_5	Reason_6	Reason_7	Reason_8	Reason_9	Reason_10	Reason_11	Reason_12
NA	DD	44104	AAG                  	AAG                                     	AAG                                     	AAG                                     	AAG                                     	AAG                                     	AAG                                     	AAG                                     	AAG                                     	AAG                                     	AAG                                     	AAG                                     	AAG                                     
NA	                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	8855	AAG                             	AAG                                     	AAG                                     	AAG                                     	AAG                                     	AAG                                     	AAG                                     	AAG                                     	AAG                                     	AAG                                     	AAG                                     	AAG                                     	AAG                                     
Accepted                                                                                                                                                                                                                                                       	                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	26910	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       
NA	                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	8855	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       
NA	DD	44104	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       	Txn                                       
NA	DD	44104	Bank	Bank	Bank	Bank	Bank	Bank	Bank	Bank	Bank	Bank	Bank	Bank	Bank
NA	                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	8855	Cache                                             	Cache                                             	Cache                                             	Cache                                             	Cache                                             	Cache                                             	Cache                                             	Cache                                             	Cache                                             	Cache                                             	Cache                                             	Cache                                             	Cache                                             
NA	                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	8855	Failed                                            	Failed                                            	Failed                                            	Failed                                            	Failed                                            	Failed                                            	Failed                                            	Failed                                            	Failed                                            	Failed                                            	Failed                                            	Failed                                            	Failed                                            
Accepted                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	26910	Real-time                                         	Real-tim                                          	Real-tim                                          	Real-tim                                          	Real-tim                                          	Real-tim                                          	Real-tim                                          	Real-tim                                          	Real-tim                                          	Real-tim                                          	Real-tim                                          	Real-tim                                          	Real-tim                                          
NA	                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                	8855	Update                                   	Update                                   	Update                                   	Update                                   	Update                                   	Update                                   	Update                                   	Update                                   	Update                                   	Update                                   	Update                                   	Update                                   	Update                                   
NA	DD	44104	Success                                           	Success                                           	Success                                           	Success                                           	Success                                           	Success                                           	Success                                           	Success                                           	Success                                           	Success                                           	Success                                           	Success                                           	Success                                           
Accepted  
;
data want;
 set have;
 retain found;
 if not found then output;
 if x='Accepted' then found=1;
 drop found;
run;

Re: SAS Programming

JJP1 — Tue, 13 Aug 2019 13:34:21 GMT

Thanks @Ksharp ,but iam not getting the expected output as desired,the same values are getting filled on other reason columns please.

@andreas_lds ,please find the sample input below :

Staus

NAME

Reason_0

Reason_1

Reason_2

Reason_3

Reason_4

Reason_5

Reason_6

Reason_7

Reason_8

Reason_9

Reason_10

Reason_11

Reason_12

44104

AAG

8855

AAG

Accepted

26910

Txn

8855

Txn

44104

Txn

44104

Bank

8855

Cache

8855

Failed

Accepted

26910

Real-time

Real-tim

8855

Update

44104

Success

Accepted

26910

Success

Expected output as below please :

NAME

Status

Reason_0

Reason_1

Reason_2

Reason_3

Reason_4

Reason_5

Reason_6

Reason_7

Reason_8

Reason_9

Reason_10

Reason_11

Reason_12

44104

AAG

Txn

Bank

Success

8855

AAG

Txn

Cache

Failed

Update

26910

Accpeted

Txn

Real-time

Success

Sorry to trouble all please help

Re: SAS Programming

JJP1 — Tue, 13 Aug 2019 13:42:11 GMT

iam sorry kindly help whether you are able to see the sample data pasetd please.it is taking more space while pasting.so i have provided screen shot please

Re: SAS Programming

andreas_lds — Wed, 14 Aug 2019 06:11:46 GMT

Why do have 12 variables with the same content. Is this always the case? If not please post data matching what you have. And please explain the logic you want applied.

Re: SAS Programming

Astounding — Wed, 14 Aug 2019 08:10:41 GMT

Here's a program that has minor flaws. The results are alphabetized by NAME, and it fails to skip REASON_5 for 8844.

proc sort data=t;
by name id status;
run;

proc transpose data=t prefix=Reason_
out=want (drop=_name_) ;
var reason_1;
by name id status;
run;

You can jump through a few hoops to preserve the original order of the NAMEs if that is necessary.

Re: SAS Programming

JJP1 — Wed, 14 Aug 2019 09:49:53 GMT

Hi @andreas_lds ,

Why do have 12 variables with the same content. Is this always the case? If not please post data matching what you have. And please explain the logic you want applied.

the values for 12 variables are taken from the source(oracle) table where these 12 reasons are
storing it as under single column called "Reason".(please note that this table can have maximum 12 reasons
or it might have less than this please for each "NAME" column).kindly have a look as sample input source code data below

 Data t;
input NAME $5. 
ID  $3.
STATUS  $5.	
Reason $4.;
datalines;
44104 DD NA  AAG 
8855     NA  AAG 
26910    AC  TXN 
8855     NA  TXN 
44104 DD NA  TXN 
44104 DD NA  BAN 
8855     NA  CAH 
8855     NA  FAL 
26910    AC  RTM 
8855     NA  UPD 
44104 DD NA  SUC 
26910    AC  SUC 
;
run;

and what in SAS i was told to do is to create the SAS dataset where to create 12 reason columns
and display ID,NAME and status and corresponding reasons for each record
and iam strugging to get as below ouput.i tried with transpose but was not properly output was not coming as desired with 12 values.

please please help

NAME

Status

Reason_1

Reason_2

Reason_3

Reason_4

Reason_5

Reason_6

Reason_7

Reason_8

Reason_9

Reason_10

Reason_11

Reason_12

44104

AAG

TXN

BAN

SUC

8855

AAG

TXN

CAH

FAL

UPD

26910

TXN

RTM

SUC

Re: SAS Programming

andreas_lds — Wed, 14 Aug 2019 12:25:02 GMT

Why should Reason_5 be skipped for Name=8855?

Using proc transpose is afaik the shortest way to get what you want.

Re: SAS Programming

JJP1 — Wed, 14 Aug 2019 12:41:36 GMT

thanks @andreas_lds .i do not want to skip any NAME or any reason please.

i tried proc transpose option.it will create just with 6 columns please.

Actually i do n't want to manually create the columns 12 for reason value,please help .i have pasted sample inout data and output in previous chat please

Re: SAS Programming

andreas_lds — Wed, 14 Aug 2019 13:32:31 GMT

@JJP1 wrote:

thanks @andreas_lds .i do not want to skip any NAME or any reason please.

i tried proc transpose option.it will create just with 6 columns please.

Actually i do n't want to manually create the columns 12 for reason value,please help .i have pasted sample inout data and output in previous chat please

I don't understand the first sentence. Where is anything skipped? I can't see any rule or reason explaining why Reason_5 is missing, but Reason_6 has a value for Name=8855. And as long as you don't provide rules, it is simply impossible to write code that matches your expected result.

To solve the issue using proc transpose is the easiest way, because adding the missing variable is simple: just create a zero-obs dataset with all variables, then use another data step to append the result of proc transpose to the zero-obs dataset.

You could, of course use by in a data step and create the requested result in one step (sorting before is still necessary), but that way you will have to write more code.

Re: SAS Programming

JJP1 — Fri, 23 Aug 2019 06:34:01 GMT

Thanks @andreas_lds ,

kindly find below SAS DI job snapshot.where iam using transpose transformation which is currently creating the reasons based on source data.in transpose out reason 5 columns are now getting created in Test environmnet please.

So in final target table also i have manually created 12 columns for reason which are currently no data as expected.

but what my challenge is : in future ,transpose output got created with 7 or 8 reason columns.then currently there is no column mapping (for reaosn 6,reason 7 .....reason12)from transpose mappiing to table loader mapping transofmration.

These job will be running automatically as part of batch.

please find the attached mapping where mapping missing for reason7 to reason12 please as there is no input from tranpose output please.

but this is not the same case,it may have many reason with maximum 12 values please.

please help.iam sorry if iam not conveyed my problem correctly please.kindly help.i did not cleary understood how append option will help on this please.kindly help please

Re: SAS Programming

andreas_lds — Tue, 20 Aug 2019 07:43:59 GMT

@JJP1 you should have mentioned that you are using SAS DI in your initial post. All suggestion made so far assumed, that you are using are sas programming environment. i don't have access to SAS DI and don't know how to fix the problem with the tools available there.

In the "normal" SAS world you can do something like this (code untested due to having no time to work on this):

data work.dummy;
  if 0 then set <name of the transposed dataset>;
  length Reason_1-Reason_12 $ 3;
  call missing(of Reason_:);
  stop;
run;

data work.final;
  set work.dummy <name of transposed dataset>;
run;

Re: SAS Programming

Patrick — Tue, 20 Aug 2019 11:31:20 GMT

@JJP1

ETL processes require stable table structures - something a Transpose doesn't provide. I've personally always considered the implementation of an out-of-the-box DIS Transpose transformation as a questionable design decision.

Changing table structures are also something difficult to work with later on (i.e. for reports) and though should imho be avoided as far as possible for anything else than adhoc jobs.

Looking at the picture you've posted it appears that you just want to transpose the data into a final target table which always gets fully replaced. So in this scenario couldn't you just have this final table as output of the transpose transformation itself (= a permanent yellow table) and get rid of the table loader? As far as I remember the transpose transformation also gives you the option to update target metadata. As long as you don't try to use this target table as source for some other ETL process nothing should break anymore.

Re: SAS Programming

JJP1 — Tue, 20 Aug 2019 12:05:50 GMT

Hi @Patrick ,

Actually i need in final table 12 reasons columns to be created please.so if i directly replace the work table with final target table.then i will get only the columns based on source data.

Actually i have mentioned input data and expected output for which SAS DI job to be created in previous replies please.

Currently.

1.Iam sorting the data using sort transformation

2.Using tranpose transformation and update the target column mapping option seeting to "Yes" please.

3.using table loader to load the data finally please.

Would you kindly advise if you have any other best ways to handle this In SASDI please

Re: SAS Programming

Patrick — Tue, 20 Aug 2019 22:41:04 GMT

@JJP1

Then you need to use user written code for the load task. Metadata driven won't work if the source table structure changes.

Re: SAS Programming

JJP1 — Wed, 21 Aug 2019 05:54:01 GMT

Sorry to trouble @Patrick.Would you please help how to achive this please using user written node please.

Re: SAS Programming

Patrick — Wed, 21 Aug 2019 08:56:38 GMT

@JJP1

The following fully working code sample will hopefully give you all the guidance you need to set this up using DIS.

DIS will generate a bit different but logically equivalent code to the sample below.

/*** Transpose Transformation **/

/* source table as input to the transpose */
data source;
  do group=1 to 5;
    do myvar=1 to 4;
      output;
    end;
  end;
  stop;
run;

/* Use Transpose transformation 
   - table "transposed" is output as "green" table object
*/
proc transpose data=source out=transposed;
  by group;
  var myvar;
run;


/** SQL Delete Transformation **/

/* automatically generated by SQL Delete Transformation */
%let _output=work.target;

/* pre-code added to SQL delete transformation 
   - (if target table SAS table. Not required if target table in a database)
*/
proc datasets lib=%scan(work.&_output,-2,.) nolist nowarn;
  delete %scan(&_output,-1,.);
  run;
quit;


/* 
   - create metadata for target table in the required stable structure (will all the potential columns in it).
   - connect this metadata table as output to the SQL Delete transformation
      - The SQL Delete transformatio will create an empty table structure if the table doesn't exist 
        (and we've just dropped the table in the previous step)
      - For a database: use the Truncate option as this speeds up the delete operation
          - and do not use the pre-code as always re-creating a table in a database is not a good practice
            .....but we need to do it if it's a SAS table as there records get only logically deleted
*/
data target;
  attrib group col1-col12 length=8;
  stop;
run;

/** User Written Transformation **/
/* connect green output table from transformation as input,
   connect target table as output (same as used above already)
*/

/* generated by transformation */
%let _input=work.transposed;
%let _output=work.target;

/* user written code */
proc append base=&_output data=&_input force nowarn;
run;