About wtien196838

wtien196838 · ‎07-06-2016

Thanks for ChrisNz. Based on your codes, I developed my program and resolved my issue. Best of all to sas user community. Regards, wtien196838

wtien196838 · ‎06-30-2016

Dear sas user community, I develop codes to sum year to date data, the code as follows: proc sql; create table ytd_data as select location, qtr as period, /* qtr in Q1,Q2,Q3,Q4 */ sum(volume) as volume, sum(var0) as var0 from (quarterly data) group by location , qtr UNION select location, YTD as period, sum(volume) as volume, sum(var0) as var0 from (quarterly data) group by location ; Quit; As a result, I get the output : location period volume var0 A Q1 10 5 (not exist this line if missing) A Q2 10 8 A Q3 20 7 A Q4 50 9 A YTD 90 29 Then I transpose it to horizontal line : Location YTD YTD_var0 Q1 Q1_var0 ... A 90 29 10 5 Now if I have missing data on quarter (for example Q1) , I assign "." to it such as Location YTD YTD_var0 Q1 Q1_var0 A 90 29 . . other quarters values How can I code the missing quarter values for all locations (about 80) ? I appreciate your support. Regards, WT196838

wtien196838 · ‎04-26-2016

Dear SAS support community users: I use a lot of sas programs to verify the excel format reports. The procedure is coded in sas: step 1: open particular excel program : options noxwait noxsync ; %let Excel_path = 'C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE' ; Data _null_ ; x = sleep(8); run; data _null_; file DDEcmds; put '[OPEN(<path.>\test.xls)]'; z = sleep(8); run; step 2: MANUAL UNHIDE all columns and rows in each worksheet step 3: run sas program Is it possible that merging step 1 and step 2 together ? My purpose is unhide all columns and rows in each worksheet by coding. Thanks for your information. Regards, William

wtien196838 · ‎04-22-2016

Thanks for all suggestions. DavidPope has some ideas which are similar to my thought. I explain a little about my programs. I ran PC SAS 9.3 and connect to unix server to get data from oracle database. A sample of my program as follows: -------------------------------------- options source2 symbolgen mprint; %include (macro library); %include (sub programs ); proc printto logs = ... lst=... run; data aaa ; proc sql ; connect to oracle ... quit; run; ... data bbb ; set aaa; manipulate data ..... ; run; proc printto; run; ------------------------------------ I create window bat file xxx.bat with: start /w "<sas path>"\sas.exe -sysin "<..path>"\program1.sas -log c:\temp\prg.log -lst c:\temp\prg.lst ...................................... My problem for this window bat file: it can not import source of %include inside each sas program it can not write out the special log file inside each program This program is working fine separately in sas environment. I want to create a sas bat program such that %include (<path>\program1.sas) %include (<path>\program2.sas) ............................ and run it in sas environrment (Local SAS), then it connects to remote server.

wtien196838 · ‎04-21-2016

Dear SAS community users, Right now I have 5 sas programs: the first four collect data and the fifth one picks up data and creates excel report. I need to open pc sas to run one by one in order (some programs connect to remote server). How can I make a batch program to run in sas environment ? I tried to gather all 5 into windows batch .bat to execute but it failed. Thanks for your solution. Regards, William

wtien196838 · ‎04-20-2016

Thanks for all replies. I coded as follows: filename rlink '<..path>\filename.scr'; signon rlink; libname rwork slibref=work server=prod; %syslput _all_ ; rsubmit; ..... endrsubmit; It works and moves all my LOCAL macro variables to REMOTE macro variables. One minor point, the filename links to a "filename.scr" which is pronpt the user to enter user name and password to sign on unix server. Is it possible that I hard code inside the code so that the users do not need to interact the screen to enter information ? Thanks for your answer.

wtien196838 · ‎04-19-2016

Dear SAS community contributors, I store weekend '20160409' into local macro variable END_OF_THIS_WEEK and '01OCT2015'd into local macro variable START_FISCAL_YEAR. I want to remote submit two macro varibles above to server to run SQL but it failed. /******* my code: ***********/ signon rlink; libname rwork slibref=work server=prod; rsubmit; %SYSLPUT END_WEEK_REMOTE = &END_OF_THIS_WEEK ; %SYSLPUT START_FY_REMOTE = &START_FISCAL_YEAR ; ..... proc sql; ...... endrsubmit; /*** error log ***/ signon rlink; NOTE: A link to PROD has been previously established. SIGNON is not necessary. libname rwork slibref=work server=prod; NOTE: Libref RWORK was successfully assigned as follows: Engine: REMOTE Physical Name: /saswork/SAS_work00810012_imapsrac1 rsubmit; NOTE: Remote submit to PROD commencing. WARNING: Apparent symbolic reference END_OF_THIS_WEEK not resolved. ERROR: A link must be established by executing the SIGNON command before you can communicate with UNKNOWN. %SYSLPUT END_WEEK_REMOTE = &END_OF_THIS_WEEK ; %SYSLPUT START_FY_REMOTE = &START_FISCAL_YEAR ; WARNING: Apparent symbolic reference START_FISCAL_YEAR not resolved. ERROR: A link must be established by executing the SIGNON command before you can communicate with UNKNOWN. I use PC SAS 9.3 and SAS/Connect to unix server. Do you have any suggestion to solve this issue ? Thanks in advance. Regards, William

wtien196838 · ‎04-14-2016

Thank you for all helpful suggestions. Reeza suggested me a hint: remove the path of excel output file (in filename). Then sas export data output correctly into cell range and into correct tabname (with space). Again thanks for all contributors. Regards, William

wtien196838 · ‎04-14-2016

Reeza, Here is the sample source code: options noxwait noxsync ; %let Excel_path = 'C:\Program Files (x86)\Microsoft Office\Office15\EXCEL.EXE' ; Data _null_ ; x = sleep(8); run; /* load template */ %sysexec "C:\temp\weekly_00000000.xls"; filename excelsys DDE 'EXCEL|C:\temp\[weekly_00000000.xls]05 Quarter Performance!R1C6:R21C13' notab lrecl=6000; libname inpath1 'c:\temp\datasets'; Data _null_ ; file excelsys ; set inpath1.filein ; put var1 '09'x var2 '09'x var3 '09'x var4 '09'x var5 '09'x var6 '09'x var7 ; run; filename cmds dde 'excel|system' ; data _null_ ; file cmds; ; put '[SELECT("R1C6:R21C13")]'; put '[SAVE.AS("C:\temp\weekly_20160331.xls")]'; ; * put '[QUIT()]'; run; filename excelsys clear; ------------------------------------------------------------------------------------------------------------ PS: this is one of worksheets that user request to add in for a report of 15 worksheets. The old code is so complex that I just want to replace my new code. If there is no way, I need to investigate the old code to put my code in.

wtien196838 · ‎04-13-2016

I did. I tried different styles: ' ' , [], () , "" (double quote to replace space) etc.. None of them worked.

wtien196838 · ‎04-13-2016

I have export sas dataset to excel file using DDE. The code has minor issue, I coded: filename excelsys DDE 'EXCEL|<..path..>[file_name.xls]05 Quarter Performance!R1C6:R21C13' notab lrecl=6000 ; if the tab name likes "Sheet1" without space, the code is OK. With the tab name likes above, it can not write the output and generate error message. ERROR: DDE session not ready. FATAL: Unrecoverable I/O error detected in the execution of the DATA step program. Aborted during the EXECUTION phase. Can you give me a hint to solve this issue. Thanks in advance. Regards, William

wtien196838 · ‎04-08-2016

Proc format assigned the value 17.10 (in TPERC) when sas read in the excel cell value. As a result, the value sas read in is 82.5000000000 which is corresponding to the display cell value 82.5. While the actual value is 82.4678901235 if we change the format on excel (decimal=10). The above code is a part of the full program. The program contains sql to connect database to extract data. Proc compare to compare two sas datasets which are read in the data from excel report and from database.

wtien196838 · ‎04-08-2016

sorry the code : informat description $100 quarter_per TPERC ; should be read input description $100 quarter_per TPERC ;

wtien196838 · ‎04-08-2016

/* Open Excel file */ PROC FORMAT; INVALUE TPERC 'N/A' = . '-' = 999 '#VALUE!' = 999 '#DIV/0!' = 999 other =[17.10] ; Filename inputf DDE "excel|tabname r10c5:r20c6 notab lrecl=32000; /* sample 2 columns */ Data test ; infile inputf dlm='09'x DSD Missover PAD; informat description $100 quarter_per TPERC ; run; --------------------------------------- Excel report: Location Qtr_percent East region 82.5 % (excel format decimal = 1) or 82.4678901235% (format decimal=10) .... ..... I need to verify the correctness of this report. SAS pull from database the real value 82.4678901235 but sas read in the excel value 82.5000000000. AS a result, proc compare showed mismatch on the cell. I CAN NOT modify the user report, change all numbers format to decimal=10. If this is the case, proc compare showed MATCHING.

wtien196838 · ‎04-07-2016

I read a range of cells in each excel worksheet. I use DDE to read the value of each cell. Use informat to code each variable. I also extract data from same user oracle database to compare. In short, user data and my data are the same. Only user present data in excel format is differ to the real value (rounding issue). Is what you see (values in the excel cell) if what you get (rounding number) in reading excel cells into sas datasset ? Example: real value is 82.4678901235, excel format 82.5 and sas read in 82.5000000000 (informat 13.10). My question is how can I read in from excel the real value so that the proc compare user data (read from excel) and my data (read from oracle database) MATCHING ?

Online Status	Offline
Date Last Visited	‎01-28-2019 11:16 PM

Re: How to insert a Holiday value into week period that contains holid...

Re: How to insert a Holiday value into week period that contains holid...

How to insert a Holiday value into week period that contains holiday ?

Re: How to insert a missing value to a missing record ?

Re: How to insert a missing value to a missing record ?

Re: How to insert a missing value to a missing record ?

Re: How to insert a missing value to a missing record ?

How to insert a missing value to a missing record ?

Re: How can access a macro do loop (or do loop only) for a list of ite...

Re: How can access a macro do loop (or do loop only) for a list of ite...

Re: How to run sas batch job in sas editor ?

Re: How to code a missing value in YTD data ?

How to code a missing value in YTD data ?

SAS and Excel DDE: unhide columns and rows of each worksheet in a exce...

Re: How to run sas batch job in sas editor ?

How to run sas batch job in sas editor ?

Re: How to transfer local macro varibles to remote server macro variab...

How to transfer local macro varibles to remote server macro variables

Re: Export SAS dataset with DDE: Excel file with worksheet name issue

Re: Export SAS dataset with DDE: Excel file with worksheet name issue

Re: Export SAS dataset with DDE: Excel file with worksheet name issue

Export SAS dataset with DDE: Excel file with worksheet name issue

Re: How to import excel values correctly into SAS dataset ?

Re: How to import excel values correctly into SAS dataset ?

Re: How to import excel values correctly into SAS dataset ?

Re: How to import excel values correctly into SAS dataset ?