SAS Programming

[JJP1]

Hi @Patrick ,

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 loade

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

[Patrick]

@JJP1 

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

[JJP1]

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

[Patrick · Accepted solution]

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

[Astounding]

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.

[JJP1]

Thanks all for your kind responses.

I was able to create the job successfully with expected output only by taking all your suggestions and inputs please.Thanks for helping me.

[Patrick]

---

@JJP1 wrote:

Thanks all for your kind responses.

I was able to create the job successfully with expected output only by taking all your suggestions and inputs please.Thanks for helping me.

---

@JJP1 

That's good to hear.

Please mark the answer which helped you the most as solution and like the other helpful ones.

Ideally also change the subject line of your question to something a bit more descriptive than "SAS Programming".

[JJP1]

Hi @Patrick ,

I tried by many ways to change the subject line.But iam unable to change please.would you please suggest.

[Patrick]

@JJP1 

You should be able to modify the subject line if you go to your initial question on top of this discussion, click on icon and then select Edit.