mediaeval Tracker

Re: Reading from sashelp.vcolumn is taking too long

mediaeval — Wed, 23 Jul 2014 10:07:01 GMT

Thanks everyone for taking the time to answer. In the end I used Quentin's solution and runtimes have reduced from 10 minutes or so to just under 3 minutes.

Re: Reading from sashelp.vcolumn is taking too long

mediaeval — Wed, 23 Jul 2014 07:19:11 GMT

Hi data _null_, how do you format your code when posting to the forum?

Re: Reading from sashelp.vcolumn is taking too long

mediaeval — Wed, 23 Jul 2014 07:15:42 GMT

Tom, I changed my code to use the %ucase function but the times doubled, approximately. This probably isn't anything to do with the change of function, it's probably other factors. When I logged on this morning, a proc print statement on sashelp.vcolumn took 3:45.

Re: Reading from sashelp.vcolumn is taking too long

mediaeval — Wed, 23 Jul 2014 07:12:29 GMT

Quentin, on login this morning, I'm seeing 5,200 observations when I run proc print on sashelp.vcolumn. Yesterday evening I saw something over 7,000. Running this morning's proc print took 3:45!

Re: Reading from sashelp.vcolumn is taking too long

mediaeval — Tue, 22 Jul 2014 15:28:58 GMT

Many thanks! This runs much more quickly. However, the variables are written to the output text file in alphabetical order. Is there an option on proc contents that will write the variables to __varlist in the order in which they were created in the input dataset?

Re: Reading from sashelp.vcolumn is taking too long

mediaeval — Tue, 22 Jul 2014 14:16:11 GMT

Here is how I am using sashelp.vcolumn. I'm unsure how to replace the references to it, such that it will work. Also, can you guys see the following code properly? It's all run together in my browser. I'm using Internet Explorer 7.

%Macro CreateRPTFile(OutputFile, Dataset, RecLength, Library=work); **This data step writes the variable names contained in the SAS dataset that is specified in the &Dataset macro variable, to the file specified in the OutputFile parameter; Filename Fname "&OutputFile"; Data _Null_; File Fname DLM = "," LRECL = &RecLength; Set sashelp.vcolumn; If _n_ = 1 Then Put "!," @; **For libname and memname to work, the names that feed into them must be in uppercase ; Where libname = Put("&Library",$upcase50.) and memname = Put("&Dataset",$upcase50.); Put name @; Run; **This datastep outputs all of the observations in the specified dataset to the external file; Data _Null_; File Fname DLM = "," LRECL = &RecLength Mod; Set &Library..&Dataset; put "*," (_all_) (:); run; %Mend;

Reading from sashelp.vcolumn is taking too long

mediaeval — Tue, 22 Jul 2014 13:23:47 GMT

I have a program that reads regularly from this table. The time taken to read data from it tends to vary, but it should only take a few seconds. When I proc print it to the screen, it's 7,000 observations or so. Proc printing it takes 55 seconds to run. Any proc or data step that accesses it takes 55 seconds or so on average, and just now it took 2½ minutes. Any idea why this might be? The PC is a slow one, but even 1GB of RAM and a 2.2GhZ processor should have no problems with this. There are other processes running in the background, but the only place in the program that hangs is where it accesses this table. I can't access my C drive on Windows Explorer, where the file is, so I can't tell if my C drive is short of space. 20434 proc print data = sashelp.vcolumn; 20435 where libname = 'WORK' and memtype = 'DATA' and substr(name,1,3) = 'VAR'; 20436 run; NOTE: No observations were selected from data set SASHELP.VCOLUMN. NOTE: There were 0 observations read from the data set SASHELP.VCOLUMN. WHERE (libname='WORK') and (memtype='DATA') and (SUBSTR(name, 1, 3)='VAR'); NOTE: PROCEDURE PRINT used (Total process time): real time 2:30.42 cpu time 7.56 seconds

Re: sharing variables between macros

mediaeval — Thu, 17 Jul 2014 15:03:07 GMT

I'll be more explicit. Here's the macro: %macro GetData(mpfile); **The macro variable InputData will be created in the GetData macro, ; %global InputData; filename TempFile temp lrecl=4000; data _null_; infile "&mpfile" lrecl=4000; file TempFile; input; if lengthc(_infile_) > 0 then do; if substr(_infile_,1,1) in ("$","!","*") then put _infile_; end; run; data _null_; slashloc = length("&mpfile") - index(reverse(trim("&mpfile")),'\') + 1; **We subtract 4 at the end to remove the file extension; InputData = substr("&mpfile",slashloc+1,length("&mpfile")-slashloc-4); call symputx('InputData',InputData); run; **proc import is cancelled if there are no observations in TempFile. No output dataset is produced. ; **Will need to test later if that output dataset exists or not before doing any processing on it; proc import datafile = TempFile out = &InputData dbms = csv replace; getnames=yes; guessingrows=5000; run; %mend; ************* In the middle data step, a macro variable is created, &InputData, which is declared as Global at the top of the macro. It isn't obvious in the program that calls the macro that this global variable is created here. An alternative would be to declare it as global in the calling program, but then someone might ask, "why is he declaring a variable that he isn't using? Where is he ising it?" Both program and macro work correctly as they are, my question is, is there a *clearer* way of doing this? Clearer to any future programmers?

Re: sharing variables between macros

mediaeval — Thu, 17 Jul 2014 14:15:41 GMT

Thanks, everyone, for taking the time to answer. I'll be more precise. Is it possible to return a value from a macro via one of its parameters? To take a very trivial example, and for purposes of illustration only, Let's say the following macro converts Sterling to Euro. %CallMacro(Var1=sterling,Var2=exch_rate, Var3=convertedamount) Is it possible to have a calculated value be returned via the third parameter? (I know that there are much easier ways to convert currency – this is to illustrate only!)

sharing variables between macros

mediaeval — Tue, 15 Jul 2014 08:06:23 GMT

I'd like some advice on sharing macro variables between macros. I've read a couple of papers describing best practice in writing macros, and the most pertinent information to me is the discussion on declaring variables as local within a macro in order to avoid any confusion in scope. I'm looking more for information on how best to "share" macro variables between macros. I know this can be done by using the global keyword at the head of the calling program and ensuring there is adequate commenting between macros. I'm coming from an Excel VBA programming background where variables can be passed between subroutines by including them as parameters in the function call. To my mind (and probably because I'm used to it), this seems clear. When I look at VBA function and subroutine code, I can immediately see the variables that are shared between functions/subroutines (variables can also be declared as global, but let's not get into that right now) I don't see this same clarity in SAS macro programming (probably because I'm not used to it!) How can I best inform anyone looking at my code how variables are shared between macros?

Reading data from mainframe via ftp in Foundation SAS

mediaeval — Tue, 24 Sep 2013 15:12:18 GMT

I’ve written a Foundation SAS program which retrieves data repeatedly (34 times in total) from an IBM Mainframe via ftp. In order to do this, I use a Filename statement like the following:

%Let Username = ‘JohnB’;

%Let Password = ‘passw’;

Filename FileRef ftp &MainframeFile Host=&Host User=&Username Pass=&Password Debug lrecl=&RecLength recfm=f;

While testing, I hard-coded the password in the program to save time. Since the password then appears in the Log, I’ve removed the password and changed the Filename statements in the program as follows:

%Let Username = ‘JohnB’;

Filename FileRef ftp &MainframeFile Host=&Host User=&Username Prompt Debug lrecl=&RecLength recfm=f;

This new approach means that the user has to type in his/her password manually, and it will be printed to the Log as a string of X’s.

Unfortunately, this approach means that the user will be asked for his/her password 34 times when the program is run. Is there a way in SAS to only pass the password through to the mainframe via ftp just once, even though multiple ftp statements are used? Or is it possible to not have the password written to the Log? Is there an option in SAS that doesn't write certain statements to the Log? This would mean taking the statement %Let Password = ‘passw’ and not writing that to the Log.

Re: How to access a SAS dataset on an IBM mainframe from my PC?

mediaeval — Thu, 15 Aug 2013 12:40:47 GMT

Yes, SAS is on the mainframe.

When I run Proc Setinit on my PC, Connect dose not appear in the list of licensed products, so I assume I do not have it. I don't know if the Mainframe is licensed for Connect.

I do not know if there is a SAS Spawner on the mainframe (I've never even heard the term before, I don't have much involvement with mainframe).

Thanks

Joe

How to access a SAS dataset on an IBM mainframe from my PC?

mediaeval — Thu, 15 Aug 2013 11:39:50 GMT

I want to access a SAS dataset on an IBM mainframe, from Foundation SAS on my PC. I have successfully used ftp in the past to read text files from the mainframe, with code as follows:

Filename filex ftp "'pr.ap.s010.g0133'" Host="oat1" User="joec" pass='mypass' debug lrecl=1251 recfm=f;

Data Stuff;

infile filex;

input @1 var1 $ebcdic8.;

Run;

Can I employ something similar to read SAS datasets from the Mainframe...what's the best way to do that? The SAS library on the mainframe is called EU.OBTEMP.SASBASE and the dataset in there is called shpr.

Thanks

Joe

Re: how to read numbers of the form '00000-34.56' ?

mediaeval — Fri, 09 Aug 2013 12:53:35 GMT

Thank you all. The input field is 11 characters long, with two decimal places. Some sample data:

00000000.00

00000142.31

00000-23.86

00005238.99

Note that for positive numbers, the plus sign is not there. If the minus sign were not appearing, with leading zeroes, the informat would simply be 11.2.

Re: how to read numbers of the form '00000-34.56' ?

mediaeval — Fri, 09 Aug 2013 10:46:59 GMT

When I run the data step,

Data negs;

set maindata;

informat premium 11.2;

newpremium = Put(premium,11.2);

Run;

It generates the error, "Invalid numeric data '0000.-34.56' at line..." for the record in question.

The number is in a string, 11 characters in length, hence the "11.2"

how to read numbers of the form '00000-34.56' ?

mediaeval — Fri, 09 Aug 2013 09:29:18 GMT

What informat should I use to read in these numbers? The number in the example is -34.56.

Tks

Joe

Re: Reading a CSV file

mediaeval — Mon, 05 Aug 2013 08:56:58 GMT

Hi Tom,

I'm using Windows. This method gives me what I need, and it works when there are no spaces in the directory name, such as

%Let myfile = Y:\Excel\Instructions.pdf;

But for filenames such as

%Let myfile = Y:\Valuation Development\Procedural Data.xls;

It generates an error in the log,

Stderr output:

File Not Found

The file does exist. It doesn't like the spaces, I think.

Thanks

Joe

Re: Reading a CSV file

mediaeval — Fri, 02 Aug 2013 13:04:55 GMT

Thanks Tushar.

I have SAS 9.1.3. When I run the above code, only three information items are returned, the Filename, the RECFM and the LRECL, so I think this method will not work, since what I want is the File Size and when the file was created/modified, but thank you.

Joe

Reading a CSV file

mediaeval — Fri, 02 Aug 2013 10:31:07 GMT

I'm reading a CSV file into a SAS dataset using Infile. In the Log, the file location is confirmed. Is it possible to print other information regarding that CSV file to the Log, such as file size, date created, file owner etc?

passing parameters to SAS macros

mediaeval — Fri, 03 May 2013 15:04:03 GMT

I want to pass a parameter to a macro as follows:

%CreateCSVFile(&OutputDirectory || "FD_Summary.csv", 'InputDataset')

This throws an error. I know I can get around it by saying:

Data _Null_;
Informat OutputFile $200.;
OutputFile = &ExperienceOutput || "FD_Summary.csv";
Call Symput('OutputFile',OutputFile);
Run;

%CreateCSVFile(&OutputFile, 'FD_Summary', 1000);

I'd like to avoid the extra data step if I can though. Is this possible?