06-03-2016
EarlyCode
Fluorite | Level 6
Member since
10-02-2013
- 30 Posts
- 1 Likes Given
- 0 Solutions
- 0 Likes Received
-
Latest posts by EarlyCode
Subject Views Posted 1275 06-03-2016 11:16 AM 991 06-01-2016 05:43 PM 1135 06-01-2016 04:23 PM 1141 06-01-2016 02:38 PM 7595 06-01-2016 02:03 PM 7597 06-01-2016 01:53 PM 7594 06-01-2016 12:07 PM 7600 06-01-2016 12:01 PM 7610 05-31-2016 05:53 PM 7618 05-31-2016 04:18 PM -
Activity Feed for EarlyCode
- Posted Excel Network Day's function on SAS Programming. 06-03-2016 11:16 AM
- Posted Re: Proc Import Issues in Unix Environment on SAS Programming. 06-01-2016 05:43 PM
- Liked Re: Proc Import Issues in Unix Environment for Tom. 06-01-2016 05:16 PM
- Posted Re: Proc Import Issues in Unix Environment on SAS Programming. 06-01-2016 04:23 PM
- Posted Re: Proc Import Issues in Unix Environment on SAS Programming. 06-01-2016 02:38 PM
- Posted Re: Proc Import Issues in Unix Environment on SAS Programming. 06-01-2016 02:03 PM
- Posted Re: Proc Import Issues in Unix Environment on SAS Programming. 06-01-2016 01:53 PM
- Posted Re: Proc Import Issues in Unix Environment on SAS Programming. 06-01-2016 12:07 PM
- Posted Re: Proc Import Issues in Unix Environment on SAS Programming. 06-01-2016 12:01 PM
- Posted Re: Proc Import Issues in Unix Environment on SAS Programming. 05-31-2016 05:53 PM
- Posted Re: Proc Import Issues in Unix Environment on SAS Programming. 05-31-2016 04:18 PM
- Posted Re: Proc Import Issues in Unix Environment on SAS Programming. 05-31-2016 03:52 PM
- Posted Proc Import Issues in Unix Environment on SAS Programming. 05-31-2016 02:28 PM
- Posted Re: Invalid Numeric data on timepart on SAS Programming. 05-10-2016 03:18 PM
- Posted Re: Invalid Numeric data on timepart on SAS Programming. 05-10-2016 03:17 PM
- Posted Re: Invalid Numeric data on timepart on SAS Programming. 05-10-2016 02:17 PM
- Posted Invalid Numeric data on timepart on SAS Programming. 05-10-2016 01:51 PM
- Tagged Invalid Numeric data on timepart on SAS Programming. 05-10-2016 01:51 PM
- Posted Re: Date Variable - If statement for weekend adjustment on SAS Programming. 12-29-2014 04:19 PM
- Posted Re: Date Variable - If statement for weekend adjustment on SAS Programming. 12-29-2014 04:06 PM
-
Posts I Liked
Subject Likes Author Latest Post 1
06-03-2016
11:16 AM
So I was browsing the SAS for Dummies blog and came across this post.
http://blogs.sas.com/content/sasdummy/2011/05/09/calculating-the-number-of-working-days-between-two-dates/
Which I thought was interesting, but I was wondering if it could be improved.
Some holidays are set, like January 1st, fourth of July, veteran's day, and always occur on the same day each year. But others are determined by weekday of the month rather than date. My office has a set list of holidays which comes down to, Jan 1st,
Third Monday of January
Last Monday of May (could be the fourth or fifth monday depending on year)
July 4th
First Monday of Sept
November 11th
4th Thursday of November
December 25th.
(And if 01/01, 07/04, 11/11, or 12/25 is ever on a Sunday, then the holiday is observed on the following Monday.)
The custom function in the blog post would require someone to update the table with the holiday dates for all these variable holidays every year. Is there a way to have a program automatically create a volatile table to calculate for these dates that would then be used in the custom function?
... View more
06-01-2016
02:38 PM
Ultimately still the same issue.
That paper uses the SAS LIBNAME engine EXCEL. Which I can get to run just fine on my machine. However, when operating on a UNIX environment, you have to use the XLSX engine. But options such as "mixed=yes" aren't valid options.
... View more
06-01-2016
02:03 PM
Tom, Which DBMS did you use for your proc import when you tried to recreate the results?
When I use DBMS=EXCEL I can force it to read in mixed columns as text.But that doesn't work on the UNIX server. But when DBMS=XLSX which does work on the UNIX server is used, the options to tell SAS it will be mixed data and not to scan for file formats aren't valid.
... View more
06-01-2016
01:53 PM
SAS is assigning formats based on what's in the first row. The result is some data is changed to whole numerical numbers dropping decimals when they should be maintained. Or a set number is replaced with a 0. Other values are dropped entirely becoming null values. etc. Even bringing in row by row then doing proc transpose and merging the data sets won't provide a solution if it converts column B to a numeric, but column AG to character, than column AG might get dropped for not matching type. Also, once broken into individual rows, there's a risk order could change and data associations would be lost. As for other utilities, there are programs available on the UNIX server that can be activated as a macro within the SAS code, but I don't have authorization to write new ones, and setting a project for that group to create something for this limited scope wouldn't get the priority to be developed. So I'm mostly limited to what can be accomplished within the SAS program itself. The Libname link provided above isn't opening for me. I've pulled from other reference material to attempt that. However, my time got redirected for the afternoon, so I haven't been able to resolve if that method will work or not.
... View more
06-01-2016
12:07 PM
Reeza, unfortunately that link doesn't work for me. It's giving a "This page doesn't exist or you've reached the view limit" message.
... View more
06-01-2016
12:01 PM
Yes Kurt, thank you for repeating the problem that I'm being forced to find a work around for.
If using excel as a transfer medium didn't have so many inherant problems, then I wouldn't be having this issue.
Unfortunately, there is nothing I can do about any of that. I don't have control. I have to work with what's provided.
Yes, the client sends data in excel.
Yes, the structured format is a horizontal table instead of the verticle table SAS looks for.
No, I can't alter the file.
No, I can't change the file type.
Yes, I'm fully aware of all the problems that this causes.
I'll even agree that it's "stupidity squared". But I can't fix stupid. I can only work around it.
So unless you have a SAS process that can somehow change an excel file into a txt file without requiring it to be manually opened and resaved. It's not a solution.
Can we please start focusing on finding a work around that will work in a unix environment?
Because constantly having the problem restated is very frustrating not helping.
... View more
05-31-2016
05:53 PM
I agree with all that. Unfortunately I have no control over the source data, and must use it as stored. Which is why the only solution I was able to work with DBMS=EXCEL is to set all the options to no. That way it doesn't try to predict the format and just brings it in as text. And if I could just set it to run manually on my machine with Windows scheduler, it would be done. But they upped priority. Which means running it on the UNIX server. So I have to use DBMS=XLSX, which doesn't have the same options. I cannot manipulate the file or data until it is pulled into the SAS program. And if I let SAS decide how to format it, some values become 0, others become null. For all the reasons that you are telling me and I'm fully aware of. Isn't there any means of telling DBMS=XLSX to pull everything as a text string? Or another means of importing a file in a UNIX environment that will pull it as a text string?
... View more
05-31-2016
04:18 PM
Adding a manual step of opening an excel file to re-save it in a different file format would run counter intuitive of the need to automate the task.
... View more
05-31-2016
03:52 PM
No, it's not in a TAB delimited file. It's in an excel file. There are no CSV or TXT files involved. The import wizard uses DBMS=EXCEL which does not work in a unix environment.
Also, it's not excel messing with the values, it's SAS since SAS requires all values in a column to be the same format. And assigns based on the first value. Which it might interpret the first column as requiring a different format than the second. Which then when proc transposed to switch it to vertical table, can again affect the variable formats a second time. This causes multiple issues with dropping some values and other value getting transformed into meaninglessness.
All columns need to be forced to be read as text until after the table is transposed so correct formats can be applied.
I need to use DBMS=XSLX and I need SAS to apply text string formats to all columns so the raw data can be converted into useable data tables.
... View more
05-31-2016
02:28 PM
So I'm working from a source file that has a horizontal Table. (Random fictional data below)
Customer 00234 00236
Sales 23 45
Service Hours 01:15 02:30
Billable $28.75 $112.50
If I just imported as stored, SAS auto decides how to format, and since all the numbers different types data was lost. (Particularily since SAS and Excel stores time formats in different ways) Also, actual report is more involved than this simplified version, but that's off topic.
So my original proc import solution was to import with this script, which brought everything in as text, that I was then able to Proc Transpose with the first column as the heading labels, and then convert formats as needed.
PROC IMPORT DATAFILE= "Customer_Tracker.xls" OUT= WORK.TEMP_REPORT
DBMS=EXCEL REPLACE;
SHEET="SHEET1 $";
RANGE="A8:C11";
GETNAMES=No;
MIXED=YES;
SCANTEXT=NO;
USEDATE=NO;
SCANTIME=NO;
RUN;
However, after getting it to run locally, they wanted it to be automated on a UNIX environment. DBMS=EXCEL doesn't work in UNIX.
So the UNIX friendly proc import is
PROC IMPORT DATAFILE= "Customer_Tracker.xlsx" OUT= WORK.TEMP_REPORT
DBMS=XLSX REPLACE;
RANGE="SHEET1 $A8:C11";
GETNAMES=No;
RUN;
The Mixed, Scantext, Usedates, and Scantime our out of syntax for DBMS=XLSX. So I'm back to square one where SAS is auto formating the columns into values that are no longer useable for the end product.
So the question remains, how can I get SAS to import using DBMS=XLSX and force it to just read everything in as text so I can control how I want columns formatted after running the proc transpose?
... View more
05-10-2016
02:17 PM
Returns numerical values, that aren't in time format. If a second Format myTime Time.; is run it will show it in a time format, but the seconds become minutes. So 00:08 becomes 480 which SAS reads as 0:08:00 instead of 0:00:08. A work around I've just come up with is min = input(substr(myTimeString,1,2),2.); sec = input(substr(myTimeString,4,2),2.); myTime = (min*60) + sec; FORMAT myTime Time.; Which does convert 00:08 to 0:00:08. But seems like a lot of extra steps that I'm not sure if it's needed.
... View more