About Anotherdream

Anotherdream · ‎04-23-2014

If your variable is varchar than you can use a simple "substr" function. data want set have; where substr(name,1,2)~='60'; run; If its a numeric, you will have to use a put option to convert it before using the substr (there are lots of other ways I imagine. this is more of a beginner method). data want; set have; where substr(strip(put(name,32.)),1,2)~='60'; run; Just a note, but I would actually use a where clause

Anotherdream · ‎01-14-2014

Hello. Sorry for the late response, loots of work issues came up. Anyways, Patrick could you elaborate a bit on what you mean by adding an extra pass through the source analyzing the date strings? Do you basically mean to read in the file as character values, and then try each date format and find which one that works, and then save that information in a dataset and overwrite the default informat statements? If so it looks like we'd have to read the file in twice, but if that is the price we have to pay for this to work then that's acceptable, I just want to make sure we're on the same page! Brandon

Anotherdream · ‎01-08-2014

Hello Arthur. So the files are indeed coming in Excel format in most cases, however in some cases they come in CSV formats. However getting them to come 100% in Excel could be done. I am actually not sure how to use dde to identify the formats, however even if I could would that help? THe dates in the format of YYYYMMDD aren't actually formatted as dates, but are just numbers on a spreadsheet that represent dates with general formatting applied. Not sure if that helped answer your question. Brandon

Anotherdream · ‎01-08-2014

Hello everyone. I Hvae a file that needs to be processed on a regular basis (daily). The issue is the file has specific date columns, of which the columns themselves do not come in the same order. All of the dates within one column are the same, but from one file to another the dates can come (and do come) in any of the following formats. MM/DD/YYYY DD/MM/YYYY YYYYDDMM MMDDYYYY etc. Does anyone know of a way to dynamically determine a date informat from a file when it comes like this? I can't use proc import because it assigns values of 20120105 as numeric fields (cause techincally they are). Is there perhaps a way to do a proc import and suggest to the system that the field has to be a valid informat from a date range? Any and all suggestions are appreciated! Brandon

Anotherdream · ‎01-03-2014

I did alandool. It appears that if you make independent execute queries as SASKiwi does above this can be done. Just make x different queries, 1 for each temp table plus 1 additional to pull it into the sas system!

Anotherdream · ‎01-02-2014

Hello again smiller! This is actually just a theoritical problem calling an imaginary table. In practice I only keep the columns I need related to the query, I was just curious as to if there was some kind of log or variable created within the SAS system that said "index XYZ was accessed" etc.. Also, I KNOW that network speed is my largest bottle neck by a simple experiment. We created a VM that was in the same location (as in Geographic location) as the servers the databases were stored on, and ran the same query, and it took 4 seconds. Over our network it took 5+ minutes. So yeah, this was more of a theory craft question not related to performance in general, but how the inner workings of SAS work (many of my questions are). My comment about the performance not being distriguishable was tested on the VM, and it took 4.01 seconds to 4.03 seconds (averages) but a t tests on the multiple tests showed the means weren't statistically different. Anyways, i'm pretty confident that the 4 seconds it is taking is again a bottle neck on the network (since techincally it still has to go over the network on the vm). Anyways, your solution of just running the query in sql server using the index tools is what I plan on doing going forward.

Anotherdream · ‎01-02-2014

Haha good timing, I just read your previous post after this one, but we're saying the exact same thing. That is kinda what I was expecting the answer to be. I'm going to have to assume it is using the Index then because the exact same sql query does use the index so that's how I'll have to treat it moving forward. Also, the index does break the data into about ~1-2% distinct groups (there are over 100 distinct text values, of differing sizes) so it actually does dramatically speed up queries. Also it's the only logical breakdown in the data above the indivdual Loan Level, which I can't filter on by definition. Thanks for the advice however!

Anotherdream · ‎01-02-2014

Hello Tom. This appears to be a general pattern in responses that I recieve (mostly from you, but it also appears to be a pattern online). Can you elaborate more onto what are the major factors that influence how quickly SAS can pull the data and write it to disk? Is this primarily memory restricted, network restricted, etc? I know for a fact my companys network is...... lets go with "not great" haha. Furthermore I am only working on a machine with 8 Gigs of ram total. Also, when I run this code andrew I get the sql code that is run, (it's a simple select X,X1,X3 from Y1 where Z1="") however how would that help in knowning if it is using the index correctly? I guess technically since I am pulling from a column that has an Index on it it "should" be using it, but I am not sure that it is. Thanks again everyone!

Anotherdream · ‎12-30-2013

Hey Reeza, thanks for your help. This actually didn't change anything in the log when I specified this option. Not sure if it was supposed to tho (this is the first time i've ever heard of the msglevel option, and the online info on it wasn't totally clear in my opinion).

Anotherdream · ‎12-30-2013

Hello! Does anyone know if there is a book or area I can search that will explain the relationship between SAS and sql server tables pulled through a libname odbc connection? FOr example, if I had the code: libname mine odbc dsn='mydsn' schema=dbo bulkload=yes dbmax_text=32000; data answer; set mine.Table1; Where module="Module 1"; run; I would expect it to run faster if the column "module" had an index on it compared to it not having an index on it. In my own testing however, I cannot determine a noticable difference in the run speed, but that might be due to the fact that the table only has ~600,000 rows on it. I am just curious if Base SAS actually takes indexes into consideration when pulled through a libname statement, and or how the libname statements treats these! Thanks! Brandon

Anotherdream · ‎12-18-2013

Hello everyone. I have code which goes through a macro loop to produce 10 different graphs by various graphs and at different levels. I would like to list all of these graphs in ONE file, so I am using HTML. THe issue is, when I do this 10 different gif files are produced, and if any of these are deleted, or not in the same directory as the HTML file, then the html file shows an X where that graphs picture should be. Is there anyway to produce an HTML output (using ods html or ods output) that doesn't need to be in the same directory of the GIF's that produce the graphs? If not, is there a way to produce only 1 GIF that contains 10+ pages of graphs instead of 10 individual graphs? An example of my code is given below. html file="I:\Projects\BalanceGraphs.html"; goptions cback=white; GRAPH CODE LOOPED, so for example; %do i=1 %to 10; proc gchart data=MYDATA; vbar dsb_amount / discrete space=1; run; quit; %end; ods html close; Thanks for your help!

Anotherdream · ‎12-16-2013

Please cancel this post. I am just going to do it with a proc sql step instead of a datastep... I would like to know why the proc sql step works and the data step does not, even through they both use the odbc connection string. If anyone can explain this to me I will credit it with correct answer. AKA why does this work..... libname green odbc dsn='odbc_mydsn' schema=dbo bulkload=yes; proc sql; create table testybaby as select * from Green.Loanlevelpopulation where year(datepart(datamonth))=2013 and qtr(datepart(datamonth))=4; run; But this doesn't. data testybaby; set green.Loanlevelpopulation; where year(datepart(datamonth))=2013 and qtr(datepart(datamonth))=4; run; Thanks all!

Anotherdream · ‎12-16-2013

Hello everyone. I have an odbc connection set up to a sql server database (example shown below). I need to pull in data from this table specified on a specific Quarter. libname green odbc dsn='odbc_mydsn' schema=dbo bulkload=yes; Normally I would do. data answer; set Green.MYtable; where qtr(datepart(DATEFIELD))=4; run; The issue Is I get the error: ERROR: A database error occurred. The database specific error follows: DATABASE error: The function QTRis not supported by the database ODBC I can specify this using an IF statement, the issue is the table is VERY large, and the difference between an If statement and a where statement is far too large. Example, a simple "where column=X" if statement runs for 3 minutes, while in a where clause it runs in 2 seconds (the column is indexed). Does anyone know a way to pass the qtr function into a sql server database, or do I have to settle using an If statement? I am thinking of using an odbc passthrough query and passing macro variables into it. Please note the field I am trying to pull from is a DATETIME, and I have already tried "SQL_FUNCTIONS=ALL", with the qtf(datefield)=4 and it does not work. If anyone has any suggestions all are welcome. Thank you! Brandon

Anotherdream · ‎12-03-2013

yeah that's actually what I was doing. I was using tranwrd to put it to the hyphenated a in unicode, and then compressing that value, but I figured there HAD to be an easier way to do it so I figured I'd ask the experts. Glad I did. Thanks again!

Anotherdream · ‎12-03-2013

Thank you kindly. I knew this was possible!

Online Status	Offline
Date Last Visited	‎11-29-2018 11:29 AM

Re: How to get rid of negative in front of zero

Re: How to get rid of negative in front of zero

Re: consecutive flagging

Re: Using retain an multiply down a collumn

Re: How do you run a stored procedure using PROC SQL?

Re: How do you run a stored procedure using PROC SQL?

Re: X command and call system not working correctly

Re: X command and call system not working correctly

Re: X command and call system not working correctly

Re: X command and call system not working correctly

Re: Macro paramater has special characters in it '('

Re: Urgent question

Get data into sql server more efficiently?

Re: Date from SAS showing 1/1/1960

Re: Urgent question

Re: Proc SQL Delete, using a join

Re: drop variable if the variable starts with 60

Re: Dynamically determine date informat from file

Re: Dynamically determine date informat from file

Dynamically determine date informat from file

Re: Creating temp table in sql server pass through?

Re: Does SAS use Sql Indexes in pulling?

Re: Does SAS use Sql Indexes in pulling?

Re: Does SAS use Sql Indexes in pulling?

Re: Does SAS use Sql Indexes in pulling?

Does SAS use Sql Indexes in pulling?

Html multiple graphs, how to?

Re: SAS qtr() function with ODBC to sql server

SAS qtr() function with ODBC to sql server

Re: Tranwrd option to not leave space?

Re: Tranwrd option to not leave space?