|
View previous topic :: View next topic
|
| Author |
Message |
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
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 |
|
 |
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8697
Location: Dubuque, Iowa, USA
|
|
|
|
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 |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
Many Thanks Robert,
The flipping columns to rows code works great!! (: |
|
| Back to top |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8697
Location: Dubuque, Iowa, USA
|
|
|
|
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 |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1742
Location: Tirupur, India
|
|
|
|
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 |
|
|
Robert Sample
Global Moderator
Joined: 06 Jun 2008
Posts: 8697
Location: Dubuque, Iowa, USA
|
|
|
|
| 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 |
|
|
|
|
