Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist 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: 1521
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: 8154
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: 1521
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: 8154
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: 1521
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: 8154
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 Insert a Row_number into table useit DB2 2 Tue Sep 19, 2017 1:07 pm
No new posts how to see when the last read access ... Mike 1304 DB2 1 Tue Sep 12, 2017 7:52 pm
No new posts BUILD OUTFIL based on condition other... balaji81_k DFSORT/ICETOOL 13 Fri Sep 08, 2017 11:06 pm
This topic is locked: you cannot edit posts or make replies. Selecting two copybooks of different ... Vignesh Sid COBOL Programming 8 Tue Sep 05, 2017 7:28 pm
No new posts PC (UTF-8) -> z/OS (EBCDIC) -> ... prino All Other Mainframe Topics 4 Fri Sep 01, 2017 1:47 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us