bentleyj1 Tracker

Re: How to identify if working in EG or SAS/Win?

bentleyj1 — Sat, 12 Jan 2019 18:21:19 GMT

Thanks for your idea. One of the things I like about SAS is that there is usually more than one way to do something. Of course, sometimes that's a bad thing too. 🙂

How to identify if working in EG or SAS/Win?

bentleyj1 — Fri, 11 Jan 2019 22:46:19 GMT

I have a need to write a program that runs manually on EG connected to a server and on SAS/Win submitting to a server and on Linux with the job submitted as batch with Cron. In the past I've used &sysscp to identify if we're running on SAS/Win or a server but how to identify EG?

EG creates automatic macro variables not created by the other environments (like _clientVersion) and I could use %SYMEXIST to see if one of them is in the global macro table, but is there an automatic variable like &sysscp that says 'EG'? I couldn't find one.

https://blogs.sas.com/content/sasdummy/2012/10/09/special-macro-vars-in-eg/

Thanks in advance.

John

Re: Lag performance over 400 million sorted records

bentleyj1 — Thu, 25 Oct 2018 21:31:26 GMT

Thanks to Everyone who replied. As usual, I'm immensely grateful for the intelligent responses I received.

My largest concern was that for each record we were creating 15 lagged variables whether or not they were needed for the calculations--the number of needed lagged variables is based on the loan_count--if loan_count=2 then we need 2 lagged variables and only if it is more than 6 do we need all 15. As a short-term fix until I can further investigate the temporary array approach I'm using this code that lags only the fields needed for the calculation.

if loan_count = 2 then do;

** here create only the lagged variables we need. ;
dpd_lag1 = lag(dpd); pay_lag1 = lag(payment_amount);

      max_dpd = max(dpd,dpd_lag1);
max_dpd_2 = dpd_lag1;
      if dq_monthly_payment > 0 then do;
      CHRVA421 = (100 * payment) / dq_monthly_payment;
      CHRVA421P = (100 * sum(payment_amount,pay_lag1)) / (dq_monthly_payment * 2);
      end;
    end;

else if loan_count = 4 then do;
  dp_lag1 = lag(dpd); pay_lag1 = lag(payment_amount); prin_lag1 = lag(payment);
  dpd_lag2 = lag2(dpd); pay_lag2 = lag2(payment_amount); prin_lag2 = lag2(payment);
  dpd_lag3 = lag3(dpd); pay_lag3 = lag3(payment_amount);

      max_dpd = max(dpd,dpd_lag1,dpd_lag2,dpd_lag3);
max_dpd_2 = max(dpd_lag1,dpd_lag2,dpd_lag3);
      if dq_monthly_payment > 0 then do;
     CHRVA421 = (100 * sum(payment,prin_lag1,prin_lag2)) / (dq_monthly_payment * 3);
     CHRVA421P = (100 * sum(payment_amount,pay_lag1,pay_lag2,pay_lag3))/(dq_monthly_payment * 4);
      end;
end;

else if loan_count > 6 then do;
  dpd_lag1 = lag(dpd); pay_lag1 = lag(payment_amount); prin_lag1 = lag(payment);
  dpd_lag2 = lag2(dpd); pay_lag2 = lag2(payment_amount); prin_lag2 = lag2(payment);
  dpd_lag3 = lag3(dpd); pay_lag3 = lag3(payment_amount); prin_lag3 = lag3(payment);
  dpd_lag4 = lag4(dpd); pay_lag4 = lag4(payment_amount); prin_lag4 = lag4(payment);
  dpd_lag5 = lag5(dpd); pay_lag5 = lag5(payment_amount); prin_lag5 = lag5(payment);

   max_dpd = max(dpd,dpd_lag1,dpd_lag2,dpd_lag3,dpd_lag4,dpd_lag5);
   max_dpd_2 = max(dpd_lag1,dpd_lag2,dpd_lag3,dpd_lag4,dpd_lag5);
   if dq_monthly_payment > 0 then do;
     CHRVA421 = (100 * sum(payment,prin_lag1,prin_lag2,prin_lag3,prin_lag4,prin_lag5))/(dq_monthly_payment*6);
     CHRVA421P = (100 * sum(payment_amount,pay_lag1,pay_lag2,pay_lag3,pay_lag4,pay_lag5))/(dq_monthly_payment*6);
      end;

end;

Re: Lag performance over 400 million sorted records

bentleyj1 — Tue, 23 Oct 2018 22:05:26 GMT

Brilliant. Thanks. I might go with the temporary array solution... it's got to be faster than adding 15 variables to each record and then dropping them at the end of the step.

Re: Lag performance over 400 million sorted records

bentleyj1 — Tue, 23 Oct 2018 21:51:54 GMT

I've thought of using an array but figured i'd ask for opinions on this solution first. The issue with a _temporary_ array is that the values are retained-- "Temporary data element values are always automatically retained, rather than being reset to missing at the beginning of the next iteration of the DATA step." http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a000201956.htm

I need the array to be refreshed/reloaded with each iteration of the DATA step because an account can have more than 5 records. So would anything be gained from an array versus assignment statements?

Lag performance over 400 million sorted records

bentleyj1 — Tue, 23 Oct 2018 21:37:20 GMT

I'm reworking a program that has a data set with about 400 million records sorted by account number with one account number per time period. There are about 300 time periods but each account can be in the data set only maybe 20 times.

We have to process by account_number because we need to derive running totals for each account number across all time periods.
Some derived variables requires lagging. Here's the existing code-. For each record a series of lagged variables are created and then referenced in the code later.

dpd_lag1=lag(dpd); pay_lag1=lag(payment_amount); prin_lag1=lag(payment);
dpd_lag2=lag2(dpd); pay_lag2=lag2(payment_amount); prin_lag2=lag2(payment);
dpd_lag3=lag3(dpd); pay_lag3=lag3(payment_amount); prin_lag3=lag3(payment);
dpd_lag4=lag4(dpd); pay_lag4=lag4(payment_amount); prin_lag4=lag4(payment);
dpd_lag5=lag5(dpd); pay_lag5=lag5(payment_amount); prin_lag5=lag5(payment);

if loan_count=2 then do;
max_dpd=max(dpd,dpd_lag1);
max_dpd_2=dpd_lag1;

        if dq_monthly_payment > 0 then do;
          chrva421=(100*payment)/dq_monthly_payment;
          chrva421p=(100*sum(payment_amount,pay_lag1))/(dq_monthly_payment*2);
       end;
       else do;
          chrva421=.;
          chrva421p=.;
      end;
    end;

else if loan_count=3 then do;
max_dpd=max(dpd,dpd_lag1,dpd_lag2);
max_dpd_2=max(dpd_lag1,dpd_lag2);

     if dq_monthly_payment > 0 then do;
         chrva421=(100*sum(payment,prin_lag1))/(dq_monthly_payment*2);
         chrva421p=(100*sum(payment_amount,pay_lag1,pay_lag2))/(dq_monthly_payment*3);
      end;
    else do;
      chrva421=.;
      chrva421p=.;
    end;
end;

** continues up to loan_count=6 ;

The program takes Hours to run on our server.

My question is, do you think it would be faster to lag the variables 'on the fly' instead of creating them as we're doing here? We'd remove the lag assignment statements and the code would look like this with LAG functions used on-the-fly. (may be parenthesis issues in here.)

if loan_count=2 then do;
max_dpd=max(dpd,lag(dpd));
max_dpd_2=lag(dpd);

       if dq_monthly_payment > 0 then do;
         chrva421=(100*payment)/dq_monthly_payment;
         chrva421p=(100*sum(payment_amount,lag(payment_amount))/(dq_monthly_payment*2);
       end;
       else do;
        chrva421=.;
        chrva421p=.;
      end;
    end;

else if loan_count=3 then do;
max_dpd=max(dpd,lag(dpd),lag2(dpd));
max_dpd_2=max(dpd_lag1,dpd_lag2);

    if dq_monthly_payment > 0 then do;
    chrva421=(100*sum(payment,lag(payment))/(dq_monthly_payment*2);
    chrva421p=(100*sum(payment_amount,lag(payment),lag2(payment))/(dq_monthly_payment*3);
    end;
    else do;
      chrva421=.;
      chrva421p=.;
    end;
end;

I'm also thinking of putting this into a macro do-loop.

Thanks in advance for your thoughts.

John

Proc Means. Median is not in output data set.

bentleyj1 — Thu, 20 Sep 2018 19:09:29 GMT

I need the median in a Proc Means output data set. It doesn't get output. But when I run the same step without output out= the median is in the listing. Any ideas? I haven't tried Proc Summary yet.

Here's the log and output I've produced. Comments explain what's going on.

** Generate 6 descriptive statistics including median/p50 and write values to an output data set. ;

380 proc means data=shared.&program(obs=500000)

381 n nmiss mean p50 std min max maxdec=0;

382 var amtFinanced;

383 output out=stats;

384 run;

NOTE: There were 500000 observations read from the data set SHARED.PRODV02_INDIRECT_TTD.

NOTE: The data set WORK.STATS has 5 observations and 4 variables.

** The note above says the output data set has 5 observations, not 6. The median is missing. ;

** In the source dataset the variable is formatted as dollar12. ;

** Here's the data set.

Obs _TYPE_ _FREQ_ _STAT_ AMTFINANCED

1 0 500000 N $500,000

2 0 500000 MIN $0

3 0 500000 MAX $34400368

4 0 500000 MEAN $18,756

5 0 500000 STD $54,091

** Now just print the 6 statistics, with no output data set. All is normal. ;

387 proc means data=shared.&program(obs=500000)

388 n nmiss mean p50 std min max maxdec=0;

389 var amtFinanced;

390 /* output out=stats;*/

391 run;

NOTE: There were 500000 observations read from the data set SHARED.PRODV02_INDIRECT_TTD.

** Here's the listing ;

The MEANS Procedure

Analysis Variable : AMTFINANCED $Financed

N N Miss Mean 50th Pctl Std Dev Minimum Maximum

------------------------------------------------------------------------------------------------

500000 0 18756 17359 54091 0 34400368

------------------------------------------------------------------------------------------------

Re: %unquoting a macro variable containing a directory path

bentleyj1 — Thu, 13 Sep 2018 19:09:10 GMT

Thanks Vince! Yeah, this could work for me. I'll give it a spin.

I just want to get rid of the message. Unexpected notes and messages make some people nervous, and I don't like them in my logs even when they're harmless.

John

%unquoting a macro variable containing a directory path

bentleyj1 — Thu, 13 Sep 2018 17:17:50 GMT

There are quite a few questions about unquoting but none provide a solution for my problem. I have a macro variable containing a directory string that is created in the SAS desktop environment and passed to a server via %syslput. %syslput requires that the macro variable be %bquoted or else it will return an 'unrecognized options' error because of the / delimiters. That's ok.

When I use the macro variable in the server environment in an X-Command it works just fine but SYMBOLGEN returns the "Some characters in the above value which were subject to macro quoting have been unquoted for printing." message. Using the %unquote function doesn't make the message go away. I'm wondering how to make it go away, other than use NOSYMBOLGEN. This does not run inside a macro.

%let dirFieldList=/risk/data/201808/filelist.txt;

%syslput dirFieldList=%bquote(&dirFieldList);

RSUBMIT;

options symbolgen;

x rm %unquote(&dirFileList);

** Returns same message when &dirFileList is not inside " " ;

496 options symbolgen;

497

498 x rm %unquote("&dirFileList");

SYMBOLGEN: Macro variable DIRFILELIST resolves to /sasapp/credrisk/wfdscrdm/filelist.txt

SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.

Re: Enterprise Guide, how to run program at startup

bentleyj1 — Tue, 28 Aug 2018 14:23:05 GMT

I can't find the workspace server initialization log (right-click the server in the Servers view, select Properties, Software tab, then click View Initialization Log button). It's possible likely that our SAS Admin has turned off initialization logging, or is it possible that the SAS Admin has disabled the run code at startup capability? Something like that wouldn't surprise me.

What I need are some macro variables and librefs assigned at startup so I don't have to assign them manually. But I don't know that will work because my experience seems to be that macro variables and librefs assigned in one project are not available in another.

Thanks.

John

Re: Enterprise Guide, how to run program at startup

bentleyj1 — Thu, 23 Aug 2018 14:27:34 GMT

Thanks for your help Casey. I'll give it another try. I didn't know about the workspace server initialization log (right-click the server in the Servers view, select Properties, Software tab, then click View Initialization Log button).

What does %let_EG_WORKSPACEINIT=1 do in your custom code?

Modifying the autoexec.sas on the server and updating the server definitions to use different autoexecs is a great idea but it will never happen here.

John

Re: How to link a sequence of SAS Enterprise Guide projects

bentleyj1 — Wed, 22 Aug 2018 19:51:29 GMT

This should jump-start some work I'll have to do in a few weeks. Thanks for sharing it.

John

Enterprise Guide, how to run program at startup

bentleyj1 — Wed, 22 Aug 2018 19:47:35 GMT

I'm a new EG user and every day I'm finding things that irritate me.

Upon starting EG I've got it set up to automatically connect to a remote server. That works fine. But I also have a second remote server that I sometimes connect to and sometimes I submit code to the local 'server'.

How do I (or can I) make it submit server-specific code (an autoexec.sas) the first time I connect to a server? That is, when I set server serverA as 'active' I want it to automatically run the serverA program and when I set serverB active I want the serverB program to run.

These programs contain server-specific options (like sasautos= ) and libname and filename statements.

With my old PC SAS and SAS/Connect it was pretty self-explanatory. But with EG, the same Tools, Options, SAS Programs, 'submit SAS code when server is connected code' runs for every server. I've tried putting an autoexec.sas in my home directory on each server but EG appears to ignore it.

In the program that's submitted to the servers when they're made active, I tried using a macro with conditional execution based on the value of _SASSERVERNAME to assign a global macro variable holding the server name. But then checking it with a different program using %put _user_; doesn't show the global macro variable I tried to create so I assume its not working.

Ha anyone figured out how to do this? Or is the answer to have a 'project' for each server and manually run it after connecting?

Thanks in advance for the help.

John

Re: How to reset EG 7.12 log summary to blank?

bentleyj1 — Tue, 21 Aug 2018 13:27:41 GMT

Thanks for the reply Chris. As luck would have it, I ordered your book just a few days ago... it might arrive today.

Unfortunately for me, EG is becoming the required tool for SAS programmers here. It's fine for data analysts who want to mouse their way thru a data step but it's too clumsy for doing any real coding. Our IT is very lazy and it they can dump the SAS/Windows software then it's less work for them.

How to reset EG 7.12 log summary to blank?

bentleyj1 — Mon, 20 Aug 2018 20:02:08 GMT

I'm an old SAS programmer transitioning to Enterprise Guide. Most things I like, some things I don't. I understand the concept of a SAS project, but I write programs, not projects. Anyway. . .

During development run a program one step at a time and capture the log step by step so I can refer back to a previous iteration. When it all works I do a final run to get a clean log for documentation. I figured out how to turn on the EG Project Log feature.

My question is about the Log Summary window. Right now it has 500 entries (all Notes). It bugs me. It's very useful, but is there a way to clear the log summary without losing the Project Log? Or do I have to save/delete the Project Log to make the Log Summary go away? I asked a couple people and nobody knows.

Thanks in advance.

John

Re: Proc Copy vs fcopy function: Can anyone explain the performance difference

bentleyj1 — Mon, 13 Aug 2018 13:47:52 GMT

Actually, 2 minutes is fast here... the system is totally I/O bound.

I'm copying the file to a different directory on the same file system.

John

Proc Copy vs fcopy function: Can anyone explain the performance difference

bentleyj1 — Sun, 12 Aug 2018 22:07:26 GMT

I recently stumbled across the FCOPY function and quite frankly it's performance appears to be too good to be true.

I'm running on 64-Bit AIX, SAS 9.4 M5. We have a permanent SAS data set created daily that's almost a gigabyte in size, about 65 million long records that we copy to a user-accessible directory so users stay out of the production directory.

Proc Copy takes about 40 minutes to copy the data set and the index in one step. I know that Fcopy copies files as binary images but it takes only about two minutes to do the same thing in two separate steps, data set then index. That's almost unbelievable so I did it a few times at different times of the day. Same performance. I've never tried copying the files with the system mv command so I don't know how long takes.

I pulled a million records from the Proc Copy copy and compared them to the same records in the Fcopy copy (thank you Proc Compare) and no unequal values were found.

I'm not looking a gift horse in the mouth, but I'd like to understand why Fcopy is sooooo much faster than Proc Copy? Is it that a binary copy process uses a bigger block size or proc copy is has a validation/verification step?

Thanks in advance.

John

Re: proc copy and unix permissions

bentleyj1 — Fri, 10 Aug 2018 20:07:06 GMT

Wow. It appears I didn't fully understand the one of the basics about Unix permissions.

Forgive me but I just verified your answer and you are correct. umask 002 gives file permissions 664 on files and 775 on directories. I thought everything started with 777!

https://www.cyberciti.biz/tips/understanding-linux-unix-umask-value-usage.html

Thanks for everyone's help.

Re: proc copy and unix permissions

bentleyj1 — Fri, 10 Aug 2018 18:00:32 GMT

Wow, this is almost like IM'ing in real time. Thanks to both of you for the fast replies.

Kurt, yes, the same instance of SAS runs the Proc Copy. Actually, it runs soon after the "final" data set is created. We move it because we don't want the users using datasets in our production directories. No, the dataset doesn't exist in the target directory... we rename it as a backup immediately before copying the new one.

Reeza, I didn't look at Proc Datasets. I'm so used to Proc Copy for this sort of thing I didn't consider proc datasets and the options it might have. I'll give it a look and a test run.

John

proc copy and unix permissions

bentleyj1 — Fri, 10 Aug 2018 17:23:23 GMT

We are submitting programs via EG 7.1 64-bit on Win10 for execution on a 64-bit AIX server.

We have a proc copy that's copying the data set just fine but the permissions in the target library aren't what we're expecting.

When the program runs, we use umask 002 and so In the source library the data set permissions are 775. But in the target library they're showing as 664. It's not impacting usability but I'd like to know what's going on.

I haven't spoken with a SysAdmin yet but is it possible that the directory permissions are set to default to 664 so that's what they're defaulting to? I thought I'd give the community first crack at telling me what's going on.

Different owner-ids own each directory that the librefs point to, but the same group-id owns both of them.

Our work around is to run the X-command to change the permissions after the copy runs.

Thanks in advance for the help.

John