cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Q1983
‎05-08-2023
  • 365 Posts
  • 122 Likes Given
  • 0 Solutions
  • 3 Likes Received

Extracting vertical data into spreadsheet

by Lapis Lazuli | Level 10 in SAS Programming
‎02-28-2018 03:02 PM
‎02-28-2018 03:02 PM
I have a dataset in a sharedrive called myshare The data I received is in a csv file with this format 1 Centerville P O Box 6372 Eisley Station Bridgeport, CT 06606   2 Iona Center P.O Box 4512 Warentden, CT 06514   I am currently copying and pasting the data in a spreadsheet with created columns to enable the code below to work and read left to right.  Is there a way to flip the data so I will not need to copy and paste to the individual variable names.   Data Chapters1; INFILE '/myshare/folder1/Chapters.csv' LRECL=32767 FIRSTOBS=2 ENCODING="LATIN1" DLM=',' MISSOVER DSD ; INPUT District : $CHAR20. Chapter : $CHAR20. Address : $CHAR17. City : $CHAR20. State : $CHAR2. ZIP :$CHAR10.   ; Run; proc sort data= Chapters1;by Chapter;run;   ... View more

Proc Export using xlsb

by Lapis Lazuli | Level 10 in SAS Programming
‎02-22-2018 02:49 PM
‎02-22-2018 02:49 PM
data have; input ln_no $ state $ loans amt; datalines;   1123 AL 1 100 1123 AL 1 100 1123 AL 2 150 1233 AL 2 150 CA 3 50 ; run; proc export data=have outfile="\server\t_DATA_HIST.xlsb" dbms=excelcs replace; server= 'saspcff'; run; I keep getting ERROR: CLI error trying to establish connection: [Microsoft][ODBC Excel Driver] Failure creating file. ERROR: Error in the LIBNAME statement. Is there special syntax that xlsb uses vs  xls and xlsx ???? ... View more

leading zeroes in character variable

by Lapis Lazuli | Level 10 in SAS Programming
‎02-19-2018 03:38 PM
‎02-19-2018 03:38 PM
data want; input ln_no $ ; datalines;   21555555 5544000 6654444 1145555555555 ; run; data want2; set want;   /*if ln_no lt 10 THEN LN_No = substr(left(LN_Nbr_R),1,10.);*/ if ln_no lt 10 THEN LN_No = put(ln_no, z10.); run;   I am attempting to add leading zeroes sufficient to make the character variable ln_no have 10 characters. ie 21555555 becomes 0021555555   if the variable is 10 or more characters, I want to bring it in as is.  Above is the code I have tried but no success ... View more

Date showing as numeric at input

by Lapis Lazuli | Level 10 in SAS Programming
‎02-10-2018 07:02 PM
‎02-10-2018 07:02 PM
%LET inputlist= INPUT   State : $CHAR2. Servicer : $CHAR25. Date : /*$CHAR10. The above is a snippet of code.  It handles input from a share folder in which the date is inputted as a numeric.  However because it is not recognized as an actual date the field shows a blank for that value.  The only way I know around this is to read it in as a character then attempt to convert to a date.  Is there a way to convert this to a date at initial input instead?  I tried placing an 8. after Date: 8. however that does not show the date ... View more

Proc Import and formatting

by Lapis Lazuli | Level 10 in SAS Programming
‎02-02-2018 02:41 PM
‎02-02-2018 02:41 PM
%let DorsHHFPath =/dorsshare/HHFit; %put &DorsHHFPath; proc import out= al_  datafile="&DorsHHFPath/al/*.csv" dbms=CSV replace ; getnames=no; datarow=3;  run;   proc import out= ga  datafile="&DorsHHFPath/ga/*.csv" dbms=CSV replace ; getnames=no; datarow=3;  run;   The results are two datasets by state with the following: var1     var2        var3 State    Company     Date Al        MyCompany  1/23/18 Al        MyComapny  1/31/18   A similar dataset for GA.  I do a separate proc import for each state (15 in all) 1.I have to use a rename for all of the vars (ie rename var1 = State)  2. In addition, Date imports as a character.  Can I define it as a date9. in the initial proc import statement? Also is there a way to define the other vars based on the names directly under it (ie var2 = company) 3.  In each row, the state will repeat itself depending on the number of entries for that state.  Is there a way to only bring in data and say delete the row if var1(state) does not equal the state I choose (ie AL)?     ... View more

Else statement in proc import macro

by Lapis Lazuli | Level 10 in SAS Programming
‎01-31-2018 02:02 PM
‎01-31-2018 02:02 PM
%let DorsHHFPath =/dorsshare/HHFit; %put &DorsHHFPath;     　 %Let StateList=ga il in ky md mi; %macro fetchstate(Statelist= ,   Currdate= ); %Let cnt=1; %Let Thisstate=%scan(&StateList, &cnt, %Str( ) ); %Do %While (&Thisstate NE );   proc import out= &Thisstate datafile="&DorsHHFPath/&Thisstate./&Thisstate._HHF_STM_ToState_&CURRDATE_KEY._01.csv"   dbms=CSV replace ; getnames=no; datarow=3;   run;   /*** Advance counter and select next state ***/ %Let cnt=%eval(&cnt + 1); %Let Thisstate=%scan(&StateList, &cnt, %Str( ) ); %End; %Mend;   %fetchstate(StateList=&StateList, Currdate=&Currdate);   &CURRDATE_KEY represents todays date.  This macro script creates a work file if the date extention is todays date.  If not the script generates an error.  Is there a way to avoid the error with some type of else statement that says 'no file' ?? ... View more

Re: proc import generates invalid sas name error

by Lapis Lazuli | Level 10 in SAS Procedures
‎01-30-2018 01:54 PM
‎01-30-2018 01:54 PM
I did restart the pc and sas sessions and the code does in fact run without issues.  Here is the log.  The code does not state how to actually produce the datasets with a proc sql or datastep.  This is where I got the code http://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n0ctmldxf23ixtn1kqsoh5bsgmg8.htm&docsetVersion=9.4&locale=en#p1pqdh4dy4h517n156xx9ftfndsd\     1 The SAS System 13:46 Tuesday, January 30, 2018 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL='HHF_Test_Program4'; 4 %LET _CLIENTPROJECTPATH=''; 5 %LET _CLIENTPROJECTNAME=''; 6 %LET _SASPROGRAMFILE=; 7 8 ODS _ALL_ CLOSE; 9 OPTIONS DEV=ACTIVEX; 10 GOPTIONS XPIXELS=0 YPIXELS=0; 11 FILENAME EGSR TEMP; 12 ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR 13 STYLE=HtmlBlue 14 STYLESHEET=(URL="file:///C:/Program%20Files/SASHome2/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css") 15 NOGTITLE 16 NOGFOOTNOTE 17 GPATH=&sasworklocation SYMBOLGEN: Macro variable SASWORKLOCATION resolves to "/egwork/SAS_work998500002E5A_ga016ada6.suntrust.com/SAS_workE3FB00002E5A_ga016ada6.suntrust.com/" 18 ENCODING=UTF8 19 options(rolap="on") 20 ; NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR 21 22 %let DorsHHFPath =/dorsshare/HHFit; 23 %put &DorsHHFPath; SYMBOLGEN: Macro variable DORSHHFPATH resolves to /dorsshare/HHFit /dorsshare/HHFit 24 25 %macro drive(dir,ext); 26 %local cnt filrf rc did memcnt name; 27 %let cnt=0; 28 29 %let filrf=mydir; 30 %let rc=%sysfunc(filename(filrf,&dir)); 31 %let did=%sysfunc(dopen(&filrf)); 32 %if &did ne 0 %then %do; 33 %let memcnt=%sysfunc(dnum(&did)); 34 35 %do i=1 %to &memcnt; 36 37 %let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.); 38 39 %if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do; 40 %if %superq(ext) = %superq(name) %then %do; 41 %let cnt=%eval(&cnt+1); 42 %put %qsysfunc(dread(&did,&i)); 43 proc import datafile="&dir\%qsysfunc(dread(&did,&i))" out=dsn&cnt 44 dbms=csv replace; 45 run; 46 %end; 47 %end; 48 49 %end; 50 %end; 51 %else %put &dir cannot be open.; 52 %let rc=%sysfunc(dclose(&did)); 53 54 %mend drive; 2 The SAS System 13:46 Tuesday, January 30, 2018 55 %drive(&DorsHHFPath,csv) MLOGIC(DRIVE): Beginning execution. SYMBOLGEN: Macro variable DORSHHFPATH resolves to /dorsshare/HHFit MLOGIC(DRIVE): Parameter DIR has value /dorsshare/HHFit MLOGIC(DRIVE): Parameter EXT has value csv MLOGIC(DRIVE): %LOCAL CNT FILRF RC DID MEMCNT NAME MLOGIC(DRIVE): %LET (variable name is CNT) MLOGIC(DRIVE): %LET (variable name is FILRF) MLOGIC(DRIVE): %LET (variable name is RC) SYMBOLGEN: Macro variable DIR resolves to /dorsshare/HHFit MLOGIC(DRIVE): %LET (variable name is DID) SYMBOLGEN: Macro variable FILRF resolves to mydir SYMBOLGEN: Macro variable DID resolves to 1 MLOGIC(DRIVE): %IF condition &did ne 0 is TRUE MLOGIC(DRIVE): %LET (variable name is MEMCNT) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable MEMCNT resolves to 23 MLOGIC(DRIVE): %DO loop beginning; index variable I; start value is 1; stop value is 23; by value is 1. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 1 SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 1 SYMBOLGEN: Macro variable NAME resolves to sc SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is FALSE MLOGIC(DRIVE): %DO loop index variable I is now 2; loop will iterate again. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 2 SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 2 SYMBOLGEN: Macro variable NAME resolves to FL SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is FALSE MLOGIC(DRIVE): %DO loop index variable I is now 3; loop will iterate again. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 3 SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 3 SYMBOLGEN: Macro variable NAME resolves to al SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is FALSE MLOGIC(DRIVE): %DO loop index variable I is now 4; loop will iterate again. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 4 SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 4 SYMBOLGEN: Macro variable NAME resolves to az SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is FALSE MLOGIC(DRIVE): %DO loop index variable I is now 5; loop will iterate again. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 5 SYMBOLGEN: Macro variable DID resolves to 1 3 The SAS System 13:46 Tuesday, January 30, 2018 SYMBOLGEN: Macro variable I resolves to 5 SYMBOLGEN: Macro variable NAME resolves to ca SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is FALSE MLOGIC(DRIVE): %DO loop index variable I is now 6; loop will iterate again. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 6 SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 6 SYMBOLGEN: Macro variable NAME resolves to dc SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is FALSE MLOGIC(DRIVE): %DO loop index variable I is now 7; loop will iterate again. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 7 SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 7 SYMBOLGEN: Macro variable NAME resolves to ehlp SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is FALSE MLOGIC(DRIVE): %DO loop index variable I is now 8; loop will iterate again. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 8 SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 8 SYMBOLGEN: Macro variable NAME resolves to ga SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is FALSE MLOGIC(DRIVE): %DO loop index variable I is now 9; loop will iterate again. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 9 SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 9 SYMBOLGEN: Macro variable NAME resolves to il SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is FALSE MLOGIC(DRIVE): %DO loop index variable I is now 10; loop will iterate again. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 10 SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 10 SYMBOLGEN: Macro variable NAME resolves to in SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is FALSE MLOGIC(DRIVE): %DO loop index variable I is now 11; loop will iterate again. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 11 SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 11 SYMBOLGEN: Macro variable NAME resolves to ky SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is FALSE 4 The SAS System 13:46 Tuesday, January 30, 2018 MLOGIC(DRIVE): %DO loop index variable I is now 12; loop will iterate again. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 12 SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 12 SYMBOLGEN: Macro variable NAME resolves to md SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is FALSE MLOGIC(DRIVE): %DO loop index variable I is now 13; loop will iterate again. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 13 SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 13 SYMBOLGEN: Macro variable NAME resolves to mi SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is FALSE MLOGIC(DRIVE): %DO loop index variable I is now 14; loop will iterate again. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 14 SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 14 SYMBOLGEN: Macro variable NAME resolves to ms SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is FALSE MLOGIC(DRIVE): %DO loop index variable I is now 15; loop will iterate again. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 15 SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 15 SYMBOLGEN: Macro variable NAME resolves to nc SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is FALSE MLOGIC(DRIVE): %DO loop index variable I is now 16; loop will iterate again. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 16 SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 16 SYMBOLGEN: Macro variable NAME resolves to nj SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is FALSE MLOGIC(DRIVE): %DO loop index variable I is now 17; loop will iterate again. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 17 SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 17 SYMBOLGEN: Macro variable NAME resolves to nv SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is FALSE MLOGIC(DRIVE): %DO loop index variable I is now 18; loop will iterate again. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 18 5 The SAS System 13:46 Tuesday, January 30, 2018 SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 18 SYMBOLGEN: Macro variable NAME resolves to lnk SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is TRUE MLOGIC(DRIVE): %IF condition %superq(ext) = %superq(name) is FALSE MLOGIC(DRIVE): %DO loop index variable I is now 19; loop will iterate again. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 19 SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 19 SYMBOLGEN: Macro variable NAME resolves to or SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is FALSE MLOGIC(DRIVE): %DO loop index variable I is now 20; loop will iterate again. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 20 SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 20 SYMBOLGEN: Macro variable NAME resolves to ri SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is FALSE MLOGIC(DRIVE): %DO loop index variable I is now 21; loop will iterate again. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 21 SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 21 SYMBOLGEN: Macro variable NAME resolves to tn SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is FALSE MLOGIC(DRIVE): %DO loop index variable I is now 22; loop will iterate again. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 22 SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 22 SYMBOLGEN: Macro variable NAME resolves to oh SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is FALSE MLOGIC(DRIVE): %DO loop index variable I is now 23; loop will iterate again. MLOGIC(DRIVE): %LET (variable name is NAME) SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 23 SYMBOLGEN: Macro variable DID resolves to 1 SYMBOLGEN: Macro variable I resolves to 23 SYMBOLGEN: Macro variable NAME resolves to db SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. MLOGIC(DRIVE): %IF condition %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) is TRUE MLOGIC(DRIVE): %IF condition %superq(ext) = %superq(name) is FALSE MLOGIC(DRIVE): %DO loop index variable I is now 24; loop will not iterate again. MLOGIC(DRIVE): %LET (variable name is RC) SYMBOLGEN: Macro variable DID resolves to 1 MLOGIC(DRIVE): Ending execution. 56 57 %LET _CLIENTTASKLABEL=; 6 The SAS System 13:46 Tuesday, January 30, 2018 58 %LET _CLIENTPROJECTPATH=; 59 %LET _CLIENTPROJECTNAME=; 60 %LET _SASPROGRAMFILE=; 61 62 ;*';*";*/;quit;run; 63 ODS _ALL_ CLOSE; 64 65 66 QUIT; RUN; ... View more

proc import generates invalid sas name error

by Lapis Lazuli | Level 10 in SAS Procedures
‎01-30-2018 10:40 AM
‎01-30-2018 10:40 AM
%let DorsHHFPath =/dorsshare/HHFit; %put &DorsHHFPath;   %macro drive(dir,ext); %local cnt filrf rc did memcnt name; %let cnt=0; %let filrf=mydir; %let rc=%sysfunc(filename(filrf,&dir)); %let did=%sysfunc(dopen(&filrf)); %if &did ne 0 %then %do; %let memcnt=%sysfunc(dnum(&did)); %do i=1 %to &memcnt;     %let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);     %if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do; %if %superq(ext) = %superq(name) %then %do; %let cnt=%eval(&cnt+1); %put %qsysfunc(dread(&did,&i)); proc import datafile="&dir\%qsysfunc(dread(&did,&i))" out=dsn&cnt   dbms=csv replace; run; %end; %end; %end; %end; %else %put &dir cannot be open.; %let rc=%sysfunc(dclose(&did));     %mend drive;   %drive(&DorsHHFPath,csv)   The above script is supposed to import all contents of the &DorsHHFPath share drive into a sas dataset.  It is a series of folders with some having .csv files inside.  Instead I get this error 26 %macro drive(dir,ext); ERROR: Invalid macro name (. It should be a valid SAS identifier no longer than 32 characters. ERROR: A dummy macro will be compiled. I researched the error on google however it does not seem to answer why this is happening in my case. ... View more

Reading csv from share drive into sas

by Lapis Lazuli | Level 10 in SAS Programming
‎01-29-2018 02:46 PM
‎01-29-2018 02:46 PM
I have a share drive with about 10 states named by folder (ie al az dc etc...)  Within those folders there may be a .csv file or may not be one. I am attempting to say if it exists import the contents of each folder into one dataset in sas.  Here is what I have so far   %let myPath =/myshare/HHFit; %put &myPath; %Let StateList= AL az ca dc ehlp FL ga il in ky  ; %macro fetchstates(Statelist= , Currdate= );   　 %Let cnt=1; %Let Thisstate=%scan(&StateList, &cnt, %Str( ) ); %Do %While (&Thisstate NE );   proc import out= &Thisstate datafile="&myPath/&Thisstate./&Thisstate._HHF_STM_ToState_&Currdate_key._01.csv"   dbms=CSV replace ; getnames=no; datarow=3;   run;   /*** Advance counter and select next state ***/ %Let cnt=%eval(&cnt + 1); %Let Thisstate=%scan(&StateList, &cnt, %Str( ) ); %End; %Mend;   %fetchstates(StateList=&StateList, Currdate=&Currdate_key);   I get the following error.  It is unable to open the parameter and says file does not exists.  In some cases theres no file however theres a file for AL (Alabama).  The program is supposed to loop through all folders and grab the cvs if it exists.   SYMBOLGEN: Macro variable THISSTATE resolves to AL SYMBOLGEN: Macro variable DORSHHFPATH resolves to /myshare/HHFit SYMBOLGEN: Macro variable THISSTATE resolves to AL SYMBOLGEN: Macro variable THISSTATE resolves to AL SYMBOLGEN: Macro variable CURRDATE_KEY resolves to 20180129 MPRINT(FETCHSTATES): proc import out= AL datafile="/myshare/HHFit/AL/AL_HHF_STM_ToState_20180129_01.csv" dbms=CSV replace ; MPRINT(FETCHSTATES): ADLM; MPRINT(FETCHSTATES): getnames=no; MPRINT(FETCHSTATES): datarow=3; MPRINT(FETCHSTATES): run; NOTE: Unable to open parameter catalog: SASUSER.PARMS.PARMS.SLIST in update mode. Temporary parameter values will be saved to WORK.PARMS.PARMS.SLIST. ERROR: Physical file does not exist, /myshare/HHFit/AL/AL_HHF_STM_ToState_20180129_01.csv. ERROR: Import unsuccessful. See SAS Log for details. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IMPORT used (Total process time): real time 0.11 seconds cpu time 0.01 seconds   RROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &Thisstate NE ERROR: The condition in the %DO %WHILE loop, , yielded an invalid or missing value, . The macro will stop executing. ERROR: The macro FETCHSTATES will stop executing. ... View more

Import data from folders

by Lapis Lazuli | Level 10 in SAS Procedures
‎01-28-2018 05:24 PM
‎01-28-2018 05:24 PM
　%let myPath =/myshare/HHFit; %put &myPath;   　 proc import out= AllState datafile="&myPath/GA/GA_HHF_STM_ToState_&Currdate_key..csv" dbms=CSV replace; run;   1. GA stands for Georgia, one of 19 state folders that appear in myshare/HHFit.  Each state has its own folder and .csv file that must be imported every day 2. The .csv files need to be read starting at the 4th row or firstobs = 4.  I believe I need a delimiter = '' and missover statement but get errors when I add it 3. Since I have 19 folders within the myshare/HHFit share drive is there a way to bring in all 19 folder contents or must I use 19 different proc import statements  4. When I read in the data I will have only rows of data and columns. Is there a way to also create a shell dataset and populate the results. ie data allstate servicer id address   ... View more

Temp Tab

by Lapis Lazuli | Level 10 in SAS Programming
‎01-26-2018 11:35 PM
‎01-26-2018 11:35 PM
I have read several posts n temp tables however let me explain my situation   proc sql; connect to sqlsvr (&medblogin); CREATE TABLE T_LN AS (select * from connection to sqlsvr ( select *  from T_LN where cat_id in (2,3,5)  option (maxdop 4)  )ln ); disconnect from sqlsvr; Quit;   This is a passthrough query that initially results in close to 900K records I use the table later in a proc report and attempting to create and place contents into a temp table in effort to get the proc report to process faster.  I created a dataset however it does not speed up processing in the proc report.  Is a temp table the answer and can I get an example ... View more

Show missing variable with 0

by Lapis Lazuli | Level 10 in SAS Programming
‎01-25-2018 08:54 PM
‎01-25-2018 08:54 PM
data test; input id days; datalines; 1  30; 2 90 3 115 ; run;   data test2; set test1; if days >=0 and days <=30 then do; range = '0-30'; else if days >=31 and days <=60 then range ='31-60'; else if days >=61 and days <=90 then range = '61-90';else if days >=91 and days <=120 then range = '91-120; cnt = 1; run; In this example nothing would show for the 31-60 category however in that case I want to to show 31-60 with a cnt =0 to reflect this so I can use it in a proc tabulate later on in the program.  ... View more

Re: Spacing issue in ODS output

by Lapis Lazuli | Level 10 in ODS and Base Reporting
‎01-17-2018 07:43 PM
‎01-17-2018 07:43 PM
That worked.  One more question.  When I use the code proc print data=al_loans_out label noobs ; by prop_state; var lnkey /style=[cellwidth=1.0in]; var BORR_LAST /style=[cellwidth=2.0in]; label lnkey ='LOAN_NUM' BORR_LAST='BORROWER_LAST_NAME'; run;   Since I am doing a group by prop_state, it segments each in its own tab based on state. At the top I get a header prop_state="the state name".  Is there a way I can eliminate that header and still have the name of the state only appear in the tab? instead of prop_state = AL just show AL ... View more

Spacing issue in ODS output

by Lapis Lazuli | Level 10 in ODS and Base Reporting
‎01-17-2018 05:53 PM
‎01-17-2018 05:53 PM
  ODS TAGSETS.ExcelXP file="&ReportOut..xml" Path="&OutDir" style=&dors_style options( sheet_interval='bygroup' );     /*%CoverSheet(&ReportName,&pbd_date_key,&Version);*/ proc sort data=al_loans out=al_loans_out; by prop_state; run; proc print data=al_loans_out label noobs width=minimum ; by prop_state; label lnkey ='LOAN_NUM' BORR_LAST='BORROWER_LAST_NAME' app_date_f ='APP_DATE' STAT_AGGR_DT_f ='CLOSED_DT' LTV ='LTV' LOAN_TYPE ='LOAN_TYPE' purp ='LN_PURPOSE' occupancy ='OCCUPANCY' CHNL_CD ='CHANNEL' BRANCHID ='BRANCHID' STTL_PCK_RCV_DT_f ='SETTLEMENT_PKG_RECEIVED_DT' PROP_ADDR ='PROP_ADDR' prop_addr2 ='PROP_ADDR2' PROP_CITY ='PROP_CITY' prop_state ='PROP_STATE' PROP_ZIP ='PROP_ZIP' BORR_ADDR = 'CURRENT_ADDR' prop_county ='PROP_COUNTY' BORR_ADDR2 ='BORR_ADDR2' BORR_CITY ='CURRENT_CITY' BORR_STATE ='CURRENT_prop_state' BORR_ZIP ='CURRENT_ZIP' MAILINGSTREET ='Mailing_Address' MAILINGSTREET2 ='Mailing_Address2' MAILINGCSZ ='MAILING CITY, prop_state, ZIP' CLOSER_NAME ='CLOSER_NAME' CLOSER_EMAIL ='CLOSER_EMAIL' U_CLOSERPHONE RESPA_REFUND_AMOUNT ='RESPA_REFUND_AMOUNT' discl_type ='POST_CLOSING_DISCLOSURE_TYPE' U_RES_REV_ST_f ='RESPA_REVIEW_STARTED' U_RES_REV_em_f ='RESPA_REVIEW_COMPLETED' File_Number ='HUD_FILE_NUMBER' SETTLEMENT_AGENT_COMPANY = 'SETTLEMENT_AGENT_COMPANY' SETTLEMENT_AGENT_EMAIL = 'SETTLEMENT_AGENT_EMAIL' SETTLEMENT_AGENT_PHONE = 'SETTLEMENT_AGENT_PHONE'; run; ods listing close; options missing=' ' mlogic mprint; TITLE; FOOTNOTE;   ODS TAGSETS.ExcelXP close; ods listing; quit; The columns for this output are stretched and I want them to extend just to the length of the label statement only.  I used width = minimum however they still stretch within the output tabs.  Is there another way to control the column widths that I am missing?  Is It that the column names are in caps or too long themselves ?             ... View more

Automated creation of datasets

by Lapis Lazuli | Level 10 in SAS Procedures
‎01-16-2018 04:21 PM
‎01-16-2018 04:21 PM
data states; set states1; run; sample output   State id AK 111 AL 122 CA 131   data AK AL CA; set states; if state = 'AK' then output ala; if state = 'AL' then output AL; if state = 'CA' then output CA; etc.....to all 50 states run; 1. I want to create a dataset for each state and eventually create a separate output tab report for each. If the state pulls no record I want a blank dataset with the states name 2. Is there a way to loop through this without manually creating 50 datasets as shown here   ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎05-08-2023 02:16 PM
Likes given to
Likes from
View All
Quick links