Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

SAS - selecting columns based upon a lookup table.

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics
View previous topic :: :: View next topic  
Author Message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1491
Location: Chennai

PostPosted: Mon Aug 08, 2011 7:00 pm    Post subject: SAS - selecting columns based upon a lookup table.
Reply with quote

Hi,
There are 2 SAS datasets named 'variables' & 'one',

Code:
VARIABLES
---------
Obs    LIST
         
 1      A
 2      B
 3      D

Code:
ONE
---
Obs    A    B    C    D    E
                           
 1     1    2    3    4    5
 2     2    3    4    5    6

Code:
REQUIRED OUTPUT:
       A         B         D
----------------------------
       1         2         4
       2         3         5

Based on the values for LIST in the dataset VARIABLES, the columns from dataset ONE needs to be selected.
Could you please let me know how this could be done in SAS.(Maybe with PROC SQL + subqueries?)

I tried using SQL and macros but I believe that there is a shorter & effecient way to do this.

Code:
PROC SQL NOPRINT;                                                     
/* EVALUATE &NVARS TO HAVE NO. OF OBS OF DATASET VARIABLES */         
   SELECT TRIM(LEFT(PUT(NOBS,8.)))                                     
     INTO :NVARS FROM SASHELP.VTABLE                                   
     WHERE UPCASE(LIBNAME) = 'WORK' AND UPCASE(MEMNAME) = 'VARIABLES';
                                                                       
/* PASS COLUMN NAMES INTO MACRO VARIABLES */                           
   SELECT LIST INTO :VAR1-:VAR&NVARS. FROM VARIABLES;                   
QUIT;                                                                 
/* MACRO TO GENERATE SQL QUERY */                                     
%MACRO SELCOLS;                                                       
PROC SQL;                                                             
   SELECT &VAR1. %DO I = 2 %TO &NVARS.;                               
                     ,&&VAR&I                                         
                 %END;                                                 
     FROM ONE;                                                         
QUIT;                                                                 
%MEND;                                                                 
%SELCOLS;                                                             

Thanks for viewing.
Back to top
View user's profile Send private message

Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8055
Location: East Dubuque, Illinois, USA

PostPosted: Mon Aug 08, 2011 7:26 pm    Post subject:
Reply with quote

Try this code:
Code:
PROC TRANSPOSE DATA=ONE
               OUT=OUTONE
               NAME=LIST ;
DATA M;
     MERGE VARS (IN=V)
           OUTONE (IN=O)
     ;
     BY LIST ;
     IF V & O ;
PROC TRANSPOSE DATA=M
               OUT=MT;
     ID LIST   ;
SAS data set MT will have the matches. You could DROP _NAME_ from MT (if you want) -- it's not really useful.
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1491
Location: Chennai

PostPosted: Mon Aug 08, 2011 8:39 pm    Post subject:
Reply with quote

Many Thanks Robert,
The flipping columns to rows code works great!! (:
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8055
Location: East Dubuque, Illinois, USA

PostPosted: Mon Aug 08, 2011 8:43 pm    Post subject:
Reply with quote

Glad to hear it worked!

And, by the way, this is the FIRST time since I was getting my Master's degree in the early 1980's that I've ever had a reason to use PROC TRANSPOSE.
Back to top
View user's profile Send private message
vasanthz

Global Moderator


Joined: 28 Aug 2007
Posts: 1491
Location: Chennai

PostPosted: Mon Aug 08, 2011 8:58 pm    Post subject:
Reply with quote

Of the few procs I have used so far, I find PROC TRANSPOSE and PROC TABULATE the hard ones to visualize the output before running the actual job.

Great that you visualized a double transpose solution after a such a long period of time.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 8055
Location: East Dubuque, Illinois, USA

PostPosted: Mon Aug 08, 2011 9:13 pm    Post subject:
Reply with quote

TABULATE can be tricky -- especially when putting together a complex report. I usually run it a couple of time before I get the output the way I want it.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> All Other Mainframe Topics All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Group Data based on a key Arun Raj DFSORT/ICETOOL 7 Thu Apr 27, 2017 11:29 pm
No new posts unload data from table with lob columns farhad_evan DB2 0 Sat Apr 22, 2017 1:32 pm
No new posts Data replication from multiple Db2 ta... kishpra DB2 9 Mon Mar 27, 2017 9:58 pm
No new posts JCL to Set Return code based on DB2 S... vinu78 DB2 17 Mon Mar 13, 2017 9:47 pm
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us