About Anotherdream

Anotherdream · ‎08-29-2013

Sorry that was a copy error. Even with the & inside thhis section of code it does not work and throws the same error. So even with %let inputfile=i:\projects\mycsv.csv; %macro DatasetNames(dsin,dsout); data &dsout; infile "&dsin." dsd lrecl=32000 truncover obs=1; length varnumCSV 8 name $2000.; input @; do varnumCSV = 1 to countw(_infile_,',','Q'); input name @; output; end; run; %mend; %DatasetNames(&inputfile,outputdataset); I am still getting errors

Anotherdream · ‎08-29-2013

Oh also, the error I am getting is the following. FAILed to open "I:\projects\mycsv.csv , Outputdataset);" : The system cannot find the file specified. So it looks like the system is not recognizing that the first macro varaible is SEPERATED from the second one by the comma in the macro call. It's almost like it's blurring both of them together, which makes no sene to me. :smileyangry:

Anotherdream · ‎08-29-2013

Tom's suggestion lead me to my problem, and I found the issue. Please see my last comment to see the exact error I was causing! As always, Thanks Tom Hello everyone. Can anyone help me understand what is going wrong with the following code? I don't know what I did incorrectly. note that the code inside the macro works (if instead of "&dsin" I simply replace it with "&inputfile" it works. It seems that taking inputfile and making it an entry into the macro call is causing my macro to no longer work, which does not make any sense to me. %let inputfile=i:\projects\mycsv.csv; %macro DatasetNames(dsin,dsout); data &dsout; infile "&dsin." dsd lrecl=32000 truncover obs=1; length varnumCSV 8 name $2000.; input @; do varnumCSV = 1 to countw(_infile_,',','Q'); input name @; output; end; run; %mend; %DatasetNames(&inputfile,outputdataset); In addition, the following code also works, it is a modifiation of the above. %let inputfile=i:\projects\mycsv.csv; %macro DatasetNames(dsin,dsout); data &dsout; infile dsin. dsd lrecl=32000 truncover obs=1; length varnumCSV 8 name $2000.; input @; do varnumCSV = 1 to countw(_infile_,',','Q'); input name @; output; end; run; %mend; %DatasetNames("&inputfile",outputdataset); So adding the quotes into the macro call instead of within the macro itself causes this to run. I don't understand why however. Thanks!

Anotherdream · ‎08-29-2013

When you run a sas program, and you have your task manager open, does the memory used spike to 100%? Also, is it on specific tasks that this is happening? Windows 7 is notoriously bad at handling memory when you are using a lot of it. It is MUCH better at using available memory more efficiently, but it also likes to gobble up a ton of extra memory. Example: Open a 5 mB excel file, and hit the save button over and over again. You'll notice that windows 7 does not let go of the memory needed to save the file. This is known as a memory leak and windows 7 is full of them. This can be troublesome with SAS. Also, do you have 64 BIT SAS and 64 bit office as well? There are also settings you can set globally that tell sas "ONLY use up to X amount of memory" that way if it wants to use 10 Gigs, but you only want it to use 4 GIGS, the program will run faster but you will be to multi task again.

Anotherdream · ‎08-29-2013

When you re-built the ODBC connections in the Data source (ODBC) manager did you give them the same name as you specified within your sas program? If not, they will not work

Anotherdream · ‎08-15-2013

IK don't believe he's saying his sort didn't work, what he's saying is the proc compare is throwing an error for every reord after number 2,400. That's because BOTH datasets have the first 2,400 records in common, however his dataset #2 is missing record 2,401 that exists in Dataset number 1. Therefore, every record after that point is now incorrect because SAS is trying to compare record 2,401 in dataset A to record 2,401 in dataset B, however datatset B's record 2,401 is NOT equal to the same one in dataset A. Correct me if I'm wrong, but i'm pretty sure this is what you mean because i've had it happen to me before. However, by using the ID variable on your Key, this should not happen. Proc Compare is supposed to get an output like this... Number of Observations in Common 9 Number of Observations in WORK.person but not in WORK.person1 1 Total Number of Observations Read from WORK.person1 9 Total Number of Observations Read from WORK.person 10 Number of Observations with Some Compared Variables Unequal 0 Number of Observations with All Compared Variables Equal: 9

Anotherdream · ‎08-14-2013

Just as a genereal question, are you running this on a local sas machine that is not connected to a server? Or are you running it on a server that is in a different storage location from your Oracle server? If so, it could be your network that is causing a lot of the slowdown. I know because I was running reports on a local machine that was accessing a server half way across the country, and a query that would take ~30 seconds on the server was taking over 45 minutes to go through the network (especially when you're returning a lot of data). Just another thing to check out

Anotherdream · ‎08-14-2013

glad to hear it, but just as an fyi, sas stores dates as numbers. Therefore, in your where clause, if your variable date_Of_birth is an actual date, you can just imput the Numberic format of each of these dates and it will work the same (plus be a heck of a lot easier). Just my 2 cents, but glad I could "help".

Anotherdream · ‎08-13-2013

Are you doing this on a sql server database, or are you trying to do it on a sas dataset? Because there are multiple ways to do it and each ahs its own benefits and downsides. Oh, if you are trying to do it on a sas dataset, the method above is the way I have done it in the past (with a slight modification). Proc SQL; delete from sample1 ss1 where ss1.id_src in (select id_src from Sample2); quit; run; I've never actually tried this on a multi key delete however. If you're doing this on a rbms database (like sql server) or the like, it's actually far more efficient to use a sql pass through function, Let me know if you need help on this. Thanks

Anotherdream · ‎08-13-2013

proc sql; select distinct "'"||put(date_of_birth,date9.)||"'d'" into :date separated by ',' from want; quit; run; %put &date; The above works, but in short, why do you want this? This is a very strange request because you have an uneven number of strings, and having the string around the d at the end (the last quote at the end of each string) does not seem necessary. Furthermore, why do you want this in general? What is the overall purpose?

Anotherdream · ‎08-09-2013

Hello everyone. Is it possible to set up an array on multi type columns and use a missing function? what I am trying to do is flag any row in which ANY variables are null between Variables starting in Column D and ending in Column Y (out of 50+ columns). However there is a wide mix of data between these columns (both character and numeric) so I was wondering if this could be done with a fancy trick using an array statement? I could always write multiple where statments, such as "where missing(VARH)=0 OR missing(VARI)=o.......", but it seems like there should be a much easier way to do this. Thanks so much! Brandon

Anotherdream · ‎08-08-2013

The anydtdte function (not anydate) function was the one I was looking for, thank you.

Anotherdream · ‎07-22-2013

Hello Tom, yeah this is actually what I did in my current implimentation of the process, I was just curious because I could have sworn I'd seen one before. Oh well, I can't seem to find it online, and if you also don't think one exists I probably just dreamt of it! I gotta stop dreaming of SAS, there's probably a pill I can take for that......

Anotherdream · ‎07-22-2013

If I remember correctly there is a way inherently in sas to convert date strings (in word format) into actual number dates. Can anyone link the code that would accomplish this for the following date examples (none have days so I would like the day to default to 1). January 2011 BECOMES 1-1-2011 March 2009 BECOMES 3-1-2009. ETC... This also needs to happen inside of a proc sql step, so the code has to work in a proc sql step (which shouldn't eliminate anything, just wanted to bring it up just in-case). If there is not a specific informat for this, please let me know! (although I could swear I did something like this years ago with an informat). Ex: proc sql; create table answer as select input(date,DATEFORMATX.) as Dateanswer from mytable; quit; run; All of the months values are fully spelled out with the 4 digit year attached. Thanks! Brandon

Anotherdream · ‎07-22-2013

Hiya Fleroo. Sorry I was not clear enough when I said dhms(yourdate,0,0,0); I assumed that you already knew about date literals and the fact that general dates were stored as numbers (so you can't pass a date macro into it without the literal). You know what they say about assumptions! But yeah, in the long run, spend some time looking up how dates are stored within sas (and all systems) and understanding things like "formatting does not change date values etc" and it will greatly benefit your knowledge of not just sas, but all coding languages. I see many new users have this exact problem when not recognizing the difference between datetimes and dates! Glad we could all help

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: Macro quoting issue

Re: Macro quoting issue

Macro quoting issue

Re: Why can't I multitask anymore?

Re: ODBC connections after upgrade to SAS EG 5.1 are gone

Re: compare

Re: Query optimisation suggestion

Re: Creating list of date macros

Re: Proc SQL Delete, using a join

Re: Creating list of date macros

Fundamental Missing with array

Re: Convert Date strings to actual dates

Re: Convert Date strings to actual dates

Convert Date strings to actual dates

Re: Date from SAS showing 1/1/1960