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: 1532
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: 8200
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: 1532
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: 8200
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: 1532
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: 8200
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 Can EDCICONV convert EBCDIC to ASCII... natt.sut IBM Tools 3 Fri Nov 17, 2017 8:46 pm
No new posts Split files upto certain limit based ... bubbu75 DFSORT/ICETOOL 5 Tue Nov 14, 2017 11:11 pm
No new posts Copy selected columns from 2 input fi... Poha Eater DFSORT/ICETOOL 3 Thu Nov 02, 2017 3:43 pm
No new posts Selecting a row from multiple tables! Vignesh Sid DB2 2 Thu Oct 26, 2017 6:09 pm
No new posts Update table without cursor Ron Klop DB2 3 Wed Oct 11, 2017 1:06 pm

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