Hi, Have you already tried to execute your first statement without the remote submit statements (rsubmit; and endrsubmit;) ? Just an idea... Kind regards, Florent ... View more

Hi Memo, When replacing the macro '%sysfunc' of second case by the macro '%qsysfunc' then you'll have a single quote displayed in your LOG. Kr, Florent ... View more

Hello, Have you already tried to clear the lib1 and lib2 after having created the lib3 ? libname lib1 clear; libname lib2 clear; Kind regards, Florent ... View more

Hello, I think you should have posted your topic in the following discussion forum: SAS Macro Facility, Data Step and SAS Language Elements To create a macro-variable from the result of a SQL query, you have to use the following proc sql: proc sql; select Profit into :Profit /* You can give the name you want but have to make sure */ from MyTable /* that your SQL query will only return 1 value. */ where Month = 'Feb'; quit; Have a look to the following link in order to get more details about the possibilities you have to generate macro-variables with PROC SQL: http://support.sas.com/documentation/cdl/en/sqlproc/62086/HTML/default/a001360983.htm Regards, Florent ... View more

data _null_;, After having made some changes (related to the SAS version that we use at the customer), I've tried to execute your example but it gives the following error. Would it be possible that I have this error message because of the SAS version ? What could I use in order to get the same result ? Could you briefly explain me the aim of that 'pipe' option (I'm not used to such customization of the infile statement) ? PeterC, I've tried with other variants of the dbms option but this does not change anything. I still have an error message in the logfile if the sheet that I try to import does not exist in the Excel file. Thanks to both of you for your time. Kind Regards, Florent 441 filename workbook "...\SCEN_PTC01_090611-1710_0714-1424.xls"; 442 data work.sheets; 443 length path script filevar command $256; 444 path = pathname('WORKBOOK'); 445 script = pathname('WORK')||'\SHEETNAMESS.vbs'; 446 filevar = script; 447 448 /* write the script */ 449 file dummy1 filevar=filevar; 450 451 put 'Const ' path=:$quote256.; 452 put 'Set objExcel = CreateObject("Excel.Application")'; 453 put 'With objExcel'; 454 put +3 '.Visible = False'; 455 put +3 '.DisplayAlerts = False'; 456 put +3 'Set objWorkbook = .Workbooks.Open(path)'; 457 put +3 'Set colSheets = .Worksheets'; 458 put +3 'For Each objSheet In colSheets'; 459 put +6 'WScript.echo objsheet.name'; 460 put +6 'Next'; 461 put +3 '.Application.Quit'; 462 put +3 'End With'; 463 464 /* close the script file by opening another, not used */ 465 filevar = pathname('WORK')||'DUMMY.vbs'; 466 file dummy1 filevar=filevar; 467 468 /* look at the script, not necessary but may be useful */ 469 infile dummy2 filevar=script end=eof; 470 do _n_ = 1 by 1 until(eof); 471 input; 472 put _n_ z3. +1 _infile_; 473 end; 474 475 /* call the script */ 476 command = 'cscript //nologo '||quote(trimn(left(script))); NOTE: SCL source line. 477 infile dummy3 pipe filevar=command end=eof length=l; ---- 23 ERROR 23-2: Invalid option name PIPE. 478 do until(eof); 479 input sheet $varying256. l; 480 output; 481 put _infile_; 482 end; 483 stop; 484 run; NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.SHEETS may be incomplete. When this step was stopped there were 0 observations and 2 variables. WARNING: Data set WORK.SHEETS was not replaced because this step was stopped. NOTE: DATA statement used: real time 0.01 seconds cpu time 0.01 seconds ... View more

Hello Kris, Here is a small example (based on the code you pasted) that you could easily adapt upon your needs 🙂 Regards, Florent DATA INPUT1 (drop= i); format MNAME $25.; /* Spaces */ do i=1 to 3; MNAME=' '; output; end; /* Text */ do i=1 to 2; MNAME='This is some text'; output; end; /* Dot */ do i=1 to 6; MNAME=.; output; end; run; proc sql; create table NEWFI as select MNAME, count(missing(MNAME)) as SUM from INPUT1 group by MNAME order by SUM desc; quit; ... View more

Here it is. This is a typical error message when the sheet you try to import does not exist in the Excel file. MPRINT(MAIN): ; MPRINT(IMPORT_SHEET): PROC IMPORT DATAFILE= "W:\04_0004\06_PRICING_CONTRACTING\05_STANDARDISED_ELEC\08_Airport_GUI\01_Dossiers\0100000-0120000\100002_TESTS_BETA_COPY\2009\SCEN_PTC01_090611-1710_0702-1348.XLS" OUT= efm_input_month_MV DBMS=EXCEL2000 REPLACE; MPRINT(IMPORT_SHEET): AEXC; MPRINT(IMPORT_SHEET): SHEET="Beta_INS_MV"; MPRINT(IMPORT_SHEET): GETNAMES=YES; MPRINT(IMPORT_SHEET): RUN; ERROR 22-322: Expecting a name. ERROR 76-322: Syntax error, statement will be ignored. ERROR: Import unsuccessful. See SAS Log for details. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE IMPORT used: real time 0.17 seconds cpu time 0.07 seconds ... View more

Hi Advoss, Thank you for the answer/example. The problem is that I'm not able to use the Excel engine as the customer doesn't own the license (and they don't want it). Thus I have to use the "proc import" facility. I know very well how to use this statement but unfortunately it gives an error while trying to import a worksheet which doesn't exist in the Excel file. This is why I created this post: to ask the SAS experts if there is a way to check the existence of a worksheet's name before executing the import of data. Thanks again for your help. Regards, Florent ... View more

Hi, Maybe can you simply use the search facility of the SAS online documentation ? E.G: http://support.sas.com/91doc/docMainpage.jsp Hope it helps. Regards, Florent ... View more

Hi, Could you please try to adapt the following code to your needs and let me know if this is what you were expecting as SQL query ? Regards, Florent proc sql; create table d as select ds1.ID, num1, num2, name from a ds1, b ds2 where ds1.ID = ds2.ID and ds2.num2 = (select max(num2) from b where ds1.ID = ID); quit; ... View more

Hi, I think that the following solution should also meet your expectations. The advantage is that your datasets do not need to be sorted in the same order and/or BY-variables. Please let me know 😉 Regards, Florent PROC SQL; update MDS a set X = (select coalesce(b.X, a.X) from UDS b where a.ID = b.ID); QUIT; ... View more

Hi, Here is a sample code I created to do the job 😉 You just need to update the parameters which are passed into the macro call. Enjoy ! Kind regards, Florent %MACRO UpcaseCharVar(lib, ds); /* lib: library's name ds: dataset's name */ proc sql noprint; select count(distinct name) into :NbrCharVar from dictionary.columns where libname = upcase("&lib") and memname = upcase("&ds") and type = 'char'; quit; %let NbrCharVar = &NbrCharVar; /* Removes Leading and Trailing Blanks */ proc sql noprint; select name into :CharVar1-:CharVar&NbrCharVar from dictionary.columns where libname = upcase("&lib") and memname = upcase("&ds") and type = 'char'; quit; %IF &NbrCharVar > 0 %THEN %DO i=1 %TO &NbrCharVar; proc sql; update &lib..&ds set &&CharVar&i = upcase(&&CharVar&i); quit; %END; %MEND; %UpcaseCharVar(Work, Test_DS); ... View more