About EarlyCode

EarlyCode · ‎05-10-2016

I have a report that has a time value in excel formatted to MM: SS. (01:32; 00:56, etc) When imported, they import as a text string. I need to convert it into a value that can be used in calculations. I've tried a couple of different methods to convert the text string. (Including changing the string to HH:MM: SS format) But every method I've tried to use to convert it returns null value and the Invalid numeric data, Time='00:26' error. Can someone help me with the correct method to convert a time text string to a value that can be used for calculations?

EarlyCode · ‎12-29-2014

So by obsessing over the idea of a if statement I was making it more difficult on myself then it needed to be... Sounds about right for me... :smileyblush:

EarlyCode · ‎12-29-2014

%LET DAY=%SYSFUNC(putn( %sysfunc(today()) ,WEEKDAY.)); %PUT DAY=&DAY; %MACRO Monday_Check; %IF &day=2 %THEN %LET DATE=%str(%')%sysfunc( putn( %sysfunc(intnx(day,%sysfunc(today()) ,-1)),yymmdd10.))%str(%'); %ELSE %LET DATE=%str(%')%sysfunc( putn( %sysfunc(intnx(day,%sysfunc(today()) ,-3)),yymmdd10.))%str(%'); Run; %Mend Monday_Check; %Monday_Check; %PUT Yesterday= &DATE Day= &Day; I'm missing something, I'm not sure if it's in the macro statement or the IF statement. But it's not running correctly.

EarlyCode · ‎12-29-2014

I have my date variable set as: %let DATE=%str(%')%sysfunc( putn( %sysfunc(intnx(day,%sysfunc(today()) ,-1)),yymmdd10.))%str(%'); Which is serving it's purpose to create a variable "DATE" that I use in the query to always check for new activity from yesterday. However on Mondays, I need to check for new activity over the whole weekend. So it needs to be %let DATE=%str(%')%sysfunc( putn( %sysfunc(intnx(day,%sysfunc(today()) ,-3)),yymmdd10.))%str(%'); (Which on Monday should return Friday's date.) Currently it's just making that minor manual adjustment on Mondays, but it seems like I should be able to have the adjustment occur automatically with an IF statement. But I'm not sure how to set the IF statement so it recognizes to only use the second one on Mondays and on Tuesday through Friday to use the first. (That is my main concern to avoid needing to manually adjust it every Monday, but should someone have a trick to have it make a similar adjustment for holidays to eliminate that manual adjustment, that would be awesome.)

EarlyCode · ‎11-25-2013

The field is "Package_No" and I will need to convert all pulled values to characters. But when I create a data step with Put(Package_No,Z7.); I get a syntax error 101 Put(Package_No,z7.) - 22 76 ERROR 22-322: Syntax error, expecting one of the following: a name, arrayname, ), -, :, [, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_, {. ERROR 76-322: Syntax error, statement will be ignored. Not sure what it's expecting.

EarlyCode · ‎11-25-2013

Slight annoyance on my part, but the database that I query has one field that is formatted as a number instead of text. As a result, when the value should be 0001234, it becomes 1234. This is only a problem when I'm cross matching between the database and established reports that have the leading zeros. It won't get an exact match unless I change one or the other. The preferred format that management likes to see is with the leading zeros. So when running the compare queries, I export to excel to use the formula =RIGHT("000000"&A1,7) This adds zeros on the front end, then only gives me last 7 numbers. (1234 becomes 0001234, While 12 becomes 0000012) Is there a way to do this with a data step in SAS? I've only found SUBSTR function, which seems to work more like the excel =MID() formula. Thank you,

EarlyCode · ‎10-14-2013

Unfortunately, EG just isn't an option for me. :smileycry: I'm still new to everything. (Hence originally posting in wrong forum... Sorry about that) How do you "ping tech support"? I assume you're not referring to my company's internal tech support but SAS tech support?

EarlyCode · ‎10-14-2013

I can see where you are going with that. The %Window/%display isn't needed if there is a better way to code it. It was just the first solution I was able to come up with when I was trying to establish a user input prompt for the &payer. variable. Without the option of prompt manager in EG being available, what would you suggest instead?

EarlyCode · ‎10-11-2013

That was a mistake. Long week...:smileyblush: :smileyshocked: Do you think "SAS Macro Facility, Data Step and SAS Language Elements" would be better? Let me know and I'll move it. Yeah, I can see that, display the options, then use if statements to set the right value. It should work, even if not very eloquent. Keep in mind I'm keeping it limited and simplified in order to be able to easily discuss and understand the solution then utilize it on a more grand scale. This is about optional criteria overall. What if I needed to drop the criteria all together. Like if I wanted all products sold to a specific payer, regardless of type of payment or lateness? (So drop the and Warehouse.Product_Sold.&Criteria. clause all together, or at lease have the system ignore it?)

EarlyCode · ‎10-11-2013

We don't use enterprise guide. So any solution needs to be able to be coded. And you seem to have confused a few of the variables. The %display method is a simple macro that opens a prompt window to allow you to enter a payer code upto 5 characters long, which creates the &payer. macro variable that is used later to look up all products for that payer. Then there are two static criteria, either I'm looking for all products of the payer that used a specific Pay Type, or I'm looking for all products of a specific payer that is not late. But I think I see where you were going with it. Store the the entire criteria as a variable. So if late='0' became the value for &criteria. I could run it %window info #5 @5 'Please enter payer:' #5 @26 payer 5 attr+underline; %display info; Proc SQL; create table PayerProductReport as Select Warehouse.Product_Sold.Product, Warehouse.Pruduct_Sold.Payer From Warehouse.Product_Sold Where Warehouse.Product_Sold.Payer=%unquote(%str(%'&payer%')) and Warehouse.Product_Sold.&Criteria.; Quit; Then SAS would view the Warehouse.Product_Sold.&Criteria. to be Warehouse.Product_Sold.Late='0' and run it. While if the &Criteria. variable was set to Pay_Type='CC' then SAS would read it as Warehouse.Product_Sold.Pay_Type='CC' Disclaimer: This is a simplified code with fictionalized names to help understand the concept. It has not been tested, and I may have typed a syntax error in there somewhere. However I wouldn't want to retype the criteria everytime (It'd be easier to stick with two different programs in that case.) so I can't use the same type of prompt window already used for the &payer. variable. So how would I code a prompt window to select which value to assign the criteria value without needing to type it?

EarlyCode · ‎10-11-2013

I have two queries that are virtually the same. Both have a main criteria of "Payer" Where that is established by a macro %window info #5 @5 'Please enter payer:' #5 @26 payer 5 attr+underline; %display info; Then it is used in a Proc SQL where clause with Payer=%unquote(%str(%'&payer%')) However, they both have a second, static criteria. One has a criteria of Pay_Type= 'CC' and the other has a criteria of Late='0' So currently, if I want to find the payer based on how they pay, I run the first one. But if I want to find the payer based on not being late, I run the second. Is there a way to combine them, and make it so that I can chose which of the static criteria is used when the program is run?

EarlyCode · ‎10-02-2013

And the yymmn6 is specifying the format to give the year first, then month, and n6= contained in 6 characters? So if it was yymmddn6 it would return 131002, or if I needed it in another order, mmyyn6 would return 102013? Varying file names by just the date is common around here, but they don't always follow the same format. So this should be very useful.

EarlyCode · ‎10-02-2013

Can you walk me through the syntax of the sysfunc? Because that seems to work, and I would love to understand how.

EarlyCode · ‎10-02-2013

I'm still new to sas, so I appreciate your help. The macro variable sounds like what I'm looking for this scenerio, while the prompt could actually be useful for another program I made. Do you have reference links or examples that could help me understand how to do this?

EarlyCode · ‎10-02-2013

I have a .sas program set up to import a dataset from excel, run analysis, and export the result to a new tab in the same excel file. However, everymonth the file name changes. So last month the file was File-2013090101(1).XLS, this month it will be File-2013100101(1).XLS. Always in the format of "File-YYYYMMDD01(1).XLS" for the first of the current month. I don't create the file, so I have no control over the naming convention. Currently, everytime I run the program I have to first go into the code and manually update the file name to the new name. Is there a way to automate that so when the file becomes available I just need to execute the .sas program and it will automatically select the newest file? I've tried searching for PROC IMPORT help, but I couldn't find this scenerio. So naturally if this was previously discussed I appologize. PROC IMPORT OUT= WORK.TABLEABC DATAFILE= "<FILE-PATH>\FILE-2013090101(1).XLS" DBMS=EXCEL REPLACE; SHEET= "sheet1$"; GETNAMES=YES; MIXED=NO; SCANTEXT=YES; USEDATE=YES; SCANTIME=YES; Run;

Online Status	Offline
Date Last Visited	‎06-03-2016 11:46 AM

Excel Network Day's function

Re: Proc Import Issues in Unix Environment

Re: Proc Import Issues in Unix Environment

Re: Proc Import Issues in Unix Environment

Re: Proc Import Issues in Unix Environment

Re: Proc Import Issues in Unix Environment

Re: Proc Import Issues in Unix Environment

Re: Proc Import Issues in Unix Environment

Re: Proc Import Issues in Unix Environment

Re: Proc Import Issues in Unix Environment

Re: Proc Import Issues in Unix Environment

Invalid Numeric data on timepart

Re: Date Variable - If statement for weekend adjustment

Re: Date Variable - If statement for weekend adjustment

Date Variable - If statement for weekend adjustment

Re: Database dropped leading zeros...

Database dropped leading zeros...

Re: Optional Criteria

Re: Optional Criteria

Re: Optional Criteria

Re: Optional Criteria

Optional Criteria

Re: PROC IMPORT changing file name

Re: PROC IMPORT changing file name

Re: PROC IMPORT changing file name

PROC IMPORT changing file name