Hi all, this may sound incompetent but I would like to explore the possibilities to proc transpose columns with different values into correspond columns.  Let's say I have a dataset with 3 columns – type, id and location. See below DATA TEST; INPUT TYPE$1 ID LOCATION$; CARDS;   A 1 E1 A 2 E2 B 1 W1 B 2 W2 C 1 N1 C 2 N2 ; RUN;   type id location A 1 E1 A 2 E2 B 1 W1 B 2 W2 C 1 N1 C 2 N2   Now I want to transpose the type column into 3 different columns shown as below- ID A B C 1     N1 1   W1   1 E1     2     N2 2 E2     2   W2     This could be simply accomplished using case when in proc sql (like below) or if then else in data step.   However, I want to find out if there is a way to do it in proc transpose?   The reason I asked is because in my real work situation, I have more than 100K+ records with many different values under "type" columns, so I try to avoid hard coding like this. Thanks.   PROC SQL; CREATE TABLE TEST2 AS SELECT   ID, CASE WHEN TYPE = 'A' THEN LOCATION ELSE " " END AS A, CASE WHEN TYPE = 'B' THEN LOCATION ELSE " " END AS B, CASE WHEN TYPE = 'C' THEN LOCATION ELSE " " END AS C FROM TEST ORDER BY ID   ; QUIT;   ... View more

I need to automate the date by only having the user to hard code the year and month. The below program works perfectly fine for the month of 10, 11 and 12 but did not work for month from 1 to 9. I realized this is due to single digit of month instead of double digit. Could anyone help on this?   Be noted I use this method to atuomate the date is because in the later of the program, there will be SQL pass through between other databases, so I have to automate the date in both of date9. (‘01nov2019’d) And yymmdd10 (2019-11-01) format.   %LET YYYY = 2019; /* year*/ %LET MM = 11; /*month*/ DATA _NULL_; CURRENT_MONTH = input(cats(&YYYY,&MM,"01"),yymmdd8.) ; SASD1 = quote(put(CURRENT_MONTH,date9.),"'")||"D" ; SASD2 = quote(put(intnx('MONTH',CURRENT_MONTH,0 ,"E"),date9.),"'")||"D" ; SQLD1 = quote(put(CURRENT_MONTH,yymmdd10.),"'") ; SQLD2 = quote(put(intnx('MONTH',CURRENT_MONTH,0 ,"E"),yymmdd10.),"'") ; CALL SYMPUT('SASD1',SASD1); CALL SYMPUT('SASD2',SASD2); CALL SYMPUT('SQLD1',SQLD1); CALL SYMPUT('SQLD2',SQLD2); RUN; %PUT &SASD1 &SASD2 &SQLD1 &SQLD2;   %PUT &SASD1 &SASD2 &SQLD1 &SQLD2; '01NOV2019'D '30NOV2019'D   '2019-11-01' '2019-11-30' ... View more

Hi All, I am trying to output several different datasets in one data step, while keeping different variables in each outputted dataset and rename the variables.   Below is the sample dataset. I want to output 3 different datasets as following - e – only keep variable "flag". No need to rename any variable. w – only keep variable "location". No need to rename any variable. n – keep "location" and "flag", rename "flag" to "flag1"   I tried two different approaches but both did not generate the result I want. In the 1st approach with if then else/do, I got 3 output datasets with both "location" and "flag1". see log below. 2nd approach I tried to put keep and rename after output but I am getting syntax error. see log below.   I need to do all these in one data step for efficiency because in my real work situation, reading the data several time in different proc sql or data step is time consuming given the size of the data. Any advices or guidance are gladly appreciated.       data test6;    input Strength Width section $1. location $10.;    datalines; 1243.51  3.036 a east 1221.95  2.995 b west 1131.67  2.983 a east 1129.70  3.019 b west 1198.08  3.106 a east 1273.31  2.947 b west 1250.24  3.018 b west 1225.47  2.980 a east 1126.78  2.965 c east 1174.62  3.033 c north 1250.79  2.941 c east 1216.75  3.037 c north ; run;   data e w n; set test6; if location = "east" then flag = "e"; else if location = "west" then flag = "w"; else if location = "north" then flag="n";   if flag="e" then do; keep flag; output e; end;   Else if flag="w" then do; keep location; output w; end;   Else if flag="n" then do; keep location flag; rename flag = flag1; output n; end;   run;     NOTE: There were 12 observations read from the data set WORK.TEST6. NOTE: The data set WORK.E has 6 observations and 2 variables. NOTE: The data set WORK.W has 4 observations and 2 variables. NOTE: The data set WORK.N has 2 observations and 2 variables. NOTE: DATA statement used (Total process time):   data e2 w2 n2; set test6; if location = "east" then flag = "e"; if location = "west" then flag = "w"; if location = "north" then flag="n"; if flag="e" then output e2 (keep = Flag); if flag="w" then output w2 (keep = location); if flag="n" then output n2 (keep = location flag rename=(flag=flag1)); run;   39 GOPTIONS ACCESSIBLE; 40 data e2 w2 n2; 41 set test6; 42 if location = "east" then flag = "e"; 43 if location = "west" then flag = "w"; 44 if location = "north" then flag="n"; 45 if flag="e" then output e2 (keep = Flag); _ 22 76 46 if flag="w" then output w2 (keep = location); _ 22 76 47 if flag="n" then output n2 (keep = location flag rename=(flag=flag1)); _ 22 76 2 The SAS System 07:48 Thursday, October 24, 2019 ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, ;, RC, _DATA_, _LAST_, _NULL_. ERROR 76-322: Syntax error, statement will be ignored. ... View more

Hi everyone,   I am trying to assign a libref with xlsx engine and seeing the below syntax error. However, when I run another libname statement which is almost identical to the first one, the libref was successfully assigned. I need some helps for the below two questions. Any advices are greatly appreciated.     1. How to fix the error in the first libname statement? (I need to assign the libref to my own user folder as I have write permission to it) 2. Why was the second libname statement could run successfully since it's almost identical to the first one? (I do not have write permission to this folder)   libname qc2 XLSX "&rf_dpath_me./&pym6./qc_output_&pym6..xlsx" /* Try to point to my own folder which I have write permission*/ GOPTIONS ACCESSIBLE; 40         libname qc2 SYMBOLGEN:  Macro variable RF_DPATH_ME resolves to /workspace/users/a12345 SYMBOLGEN:  Macro variable PYM6 resolves to 201909 SYMBOLGEN:  Macro variable PYM6 resolves to 201909 40       !                 XLSX "&rf_dpath_me./&pym6./qc_output_&pym6..xlsx" 41         42         GOPTIONS NOACCESSIBLE;            ________            22 ERROR: Libref QC2 is not assigned. ERROR: Error in the LIBNAME statement. ERROR 22-7: Invalid option name GOPTIONS.     However, when I run the below, the libref was successfully assigned.   libname qc2 XLSX "&rf_dpath./&pym6./qc_output_&pym6..xlsx"; /*  I don't have write permission to this folder*/ GOPTIONS ACCESSIBLE; 40         libname qc2 SYMBOLGEN:  Macro variable RF_DPATH resolves to /workspace/data/project SYMBOLGEN:  Macro variable PYM6 resolves to 201909 SYMBOLGEN:  Macro variable PYM6 resolves to 201909 40       !                 XLSX "&rf_dpath./&pym6./qc_output_&pym6..xlsx"; NOTE: Libref QC2 was successfully assigned as follows:       Engine:        XLSX       Physical Name: /workspace/data/project/201909/qc_output_201909.xlsx   ... View more