ScottBass Tracker

Re: How do I unsubscribe from a topic?

ScottBass — Tue, 09 Feb 2021 22:27:58 GMT

Doh! So I read your message, replied, went to my profile to find old notifications, which would quickly let me return to the topic in question.

Which of course refreshed my browser.

I could swear I'd done that before, but in any case it's working now:

Re: How do I unsubscribe from a topic?

ScottBass — Tue, 09 Feb 2021 22:23:42 GMT

Hi Reeza,

Always good to hear from you.

I posted a reply, so that subscribed me. The topic is actually solved, so I don't need to follow it anymore.

But can't see how to unsubscribe, which I've used in the past. I like the forum UI upgrades but this may be a case of me reverting back to forum newbie (as far as UI) status! 🙂

How do I unsubscribe from a topic?

ScottBass — Tue, 09 Feb 2021 21:53:05 GMT

Apologies if this has been asked before - I did search.

How do I unsubscribe from a topic? I'm getting too many emails on this one.

I see Subscribe, but no Unsubscribe.

Thanks!

Re: How to: Listing all the files in a folder

ScottBass — Tue, 09 Feb 2021 09:15:42 GMT

@LittlesasMaster wrote:

You can also see an example from SAS Documentation , a dynamic way of doing this

A Macro which gets you Sub Dir and File Names

%macro list_files(dir,ext);
%local filrf rc did memcnt name i;
%let rc=%sysfunc(filename(filrf,&dir));
%let did=%sysfunc(dopen(&filrf));

%if &did eq 0 %then %do; 
%put Directory &dir cannot be open or does not exist;
%return;
%end;

%do i = 1 %to %sysfunc(dnum(&did));

%let name=%qsysfunc(dread(&did,&i));

%if %qupcase(%qscan(&name,-1,.)) = %upcase(&ext) %then %do;
%put &dir\&name;
%end;
%else %if %qscan(&name,2,.) = %then %do; 
%list_files(&dir\&name,&ext)
%end;

%end;
%let rc=%sysfunc(dclose(&did));
%let rc=%sysfunc(filename(filrf));

%mend list_files;
%list_files(c:\temp,sas)

This macro looping would be orders of magnitude slower than using corresponding functions in a data step, although it does have the benefit of being recursive.

Re: How to: List all the files in a folder

ScottBass — Tue, 09 Feb 2021 09:13:37 GMT

@Peter_C wrote:
Hi Kevin
My suggestion was only for readkng contents of the files. I wouldn't use it for file names. However, with some scenarios (e.g. expecting only small files) your proposal works, but excludes empty files. I don't remember what happens for subfolders.

Peter

1) If the red text above is correct, then your solution, while it works, isn't on topic. Well, the code is on topic, just not your red text above. The subject was "List all the files in a folder".

2) Even if the proposed code works, I just can't see how reading every line in a file just to get the filename is a good approach? Sure, for small text files in a directory, it might even work faster than the SAS functions used in previous solutions - the data step is very fast at reading text files.

But put 100,000 1MB each XML or binary files in a directory and test the performance of the all the various solutions posted in this thread. You don't know a priori the contents of the directory and can't assume its contents.

Re: How to improve performance in Data step where clause

ScottBass — Thu, 22 Oct 2020 08:47:33 GMT

@Learn_SAS1 wrote:

effective_from_dtm has values like DDMONYYYY:00:00:00.000.

As I have observed most of the values has 00 as time.

Index, I have mentioned in my original post, effective_from_dtm has index and no other indexes.

I can't create Index on that table as it is owned by other teams.

That data set is not sorted.

Thanks

You've got a source dataset with 322,596,778 records, and need to filter on start_from_dtm <= some_static_date <= start_to_dtm. Typical SCD2 type filtering.

Yet your source dataset doesn't have an index on that column.

Have you have explained the performance issue to your "other teams", requested an index be created on that column, and they refused?

That's going to be your best approach to improving the performance of this query. Google how indexes work if you need further details.

Do you have the disk space to (as a proof-of-concept) create a copy of that dataset, create your own index on start_from_dtm start_to_dtm, then test the performance results?

If you do, you may also want to test performance if the data is stored using the SPDE engine.

Re: Using SAS to find a streak of occurrences within any seven day window

ScottBass — Thu, 22 Oct 2020 08:51:09 GMT

I was thinking the same approach, but you beat me to it.

We often spend so much effort preventing an unwanted Cartesian product in a multi-table join, that we forget that a Cartesian product, usually with a table self-join, can be our friend.

Having every row within a key combined with every other row within that key, then filtering as required, can be very powerful.

Edit: as long as the table isn't, say, 100M rows 😉

Re: Proc transpose error with same name variable and exceeds 32 length

ScottBass — Thu, 22 Oct 2020 08:18:17 GMT

Post what you want your desired output to be, as a self-contained SAS datastep using datalines statement, posted using the "Insert SAS code" icon.

Re: using rsubmit in a loop

ScottBass — Thu, 22 Oct 2020 08:14:22 GMT

@Ullsokk wrote:

Thanks, that looks really interesting. I am running code om the current server. I basically want all programs to run at the same time, in different sessions.

My code runs SAS in batch mode, i.e. sas.exe -sysin myprogram.sas. It uses the first SAS session as the "scripting engine" to launch multiple SAS sessions, and abort if upstream dependencies fail. When I used it I had SAS DMS on the laptop, so the scripting engine ran in interactive mode, but the remaining sessions ran in batch. As much as you can get batch on windows - there were a bunch of icons in the taskbar. They don't run in "true" background as you can get with *nix.

If you just want to run all programs at once, either modify the macro or simply set all programs to the same "level", i.e. all "1".

Note that more is not necessarily better; when I ran this on (an old) two core CPU laptop, running more than two programs at a time increased the total execution run time for all the programs. YMMV. So you may want to experiment with the max_threads setting.

Re: using rsubmit in a loop

ScottBass — Thu, 22 Oct 2020 02:34:39 GMT

I've only skimmed your post, so I may misunderstand what you're trying to do. And I can't tell if you're using rsubmit to submit code on a remote server, or the current one.

However, have a look at

https://github.com/scottbass/SAS/blob/master/Macro/RunAll.sas and https://github.com/scottbass/SAS/blob/master/Macro/RunAll_ControlTable.sas

Perhaps they will help with what you're trying to do?

Re: Delete first word before first space

ScottBass — Wed, 21 Oct 2020 09:21:43 GMT

@skavli wrote:

Hi y'all i hope you're doing fine,

I'm working on a cars database and i'm having a little hard time extract name and surname of cars, my table looks like this :

Brand

CITROEN C3 AIRCROSS

DS3 CROSSBACK E-TENSE

OPEL GRANDLAND X

Now i wanna create two variables one named Make ( for the brand name ) and the other Model (for the brand model), so far so good for the Make ( because it's the first string before the space ) so with this i've succeeded getting my Make
Make = scan(brand,1,' ');
Now it's a little bit harder to for the model i've had few ideas but none was effective, for my model variable i want everything after the first space, i thought first to ease the process use tranwrd function and replace all my spaces with a underscore but it replaces also the space in the end with underscore, for the substr function i need the position for each observation wich a little bit hard since each observation is different.

Do you have any hint for me to make this work and all the strings after the first space please ?

Edit your original post, using a SAS datastep with datalines statements to create your sample dataset. Don't paste in a screenshot since if forces *us* to create *your* sample dataset. Post using the "Insert SAS code" icon.

data have;
   length somevariable $200;
   somevariable='foo    bar blah';
run;

data want1;
   set have;
   length make $100 model $100;
   pos=index(somevariable,' ');
   if pos then do;
      make=substr(somevariable,1,pos);
      model=substr(somevariable,pos+1);
      model=strip(model);
   end;
run;

data want2;
   set have;
   length make $100 model $100;
   rx=prxparse('/(.*?) (.*)/o');
   if prxmatch(rx,somevariable) then do;
      make=prxposn(rx,1,somevariable);
      model=prxposn(rx,2,somevariable);
   end;
run;

Re: Finding the variable causing duplication in my data set

ScottBass — Wed, 21 Oct 2020 02:20:46 GMT

@ChrisNZ wrote:

Like this?

data HAVE;
  array A[500] A1-A500;
  do ID=1 to 10;
    if ID=5 then do; A55 = 2; output; end;
    output;
  end;
run;

data _null_;
  set HAVE;
  by ID;
  array ARR A1--A500;                        * index variables into an array;
  if first.ID & ^last.ID then do I=1 to 500; * duplicate found, fill lag values;
    ARR[I]=lag(ARR[I]);
  end;
  if ^first.ID;                              * keep repeated observation;
  do I=1 to dim(ARR);                        * seek different value(s)  ;
    if ARR[I] ne lag(ARR[I]) then putlog _N_= ID= ARR[I]=;
  end;
run;

_N_=6 ID=5 A55=2

Replace

array ARR A1--A500;

with

array ARR 2 variable names from your table (the double dash makes that easy, look it up) ;

Untested, just a "thought experiment"...

Would:

proc sort data=foo out=bar dupout=dups noduprecs;
   by key1 key2 key3 _all_;
run;

also work?

Re: call symput with conditional clause

ScottBass — Mon, 19 Oct 2020 08:55:27 GMT

@BrahmanandaRao wrote:
IF NOT AVAILABLE IT SHOULD BE SHOWS IN NOT AVAILABLE IN LOG WINDOW AS PER THE IF ELSE CONDITION

If you're using SAS EG or DMS, read the doc on the data step debugger.

Re: call symput with conditional clause

ScottBass — Mon, 19 Oct 2020 08:53:46 GMT

@BrahmanandaRao wrote:

data ds;
set sashelp.cars;
if Make='volkswagan'  then call symput("Status",'Available');
else call symput("Status",'Not Avaialable');
run;
%put &Status.;

it shows wrong output

WHAT OUTPUT ARE YOU EXPECTING?

Re: How to create dummy variable for every single date during a period

ScottBass — Mon, 19 Oct 2020 08:50:46 GMT

Post your code using the Insert SAS Code icon, as @ErikLund_Jensen has done. Unformatted SAS code, esp. when using datalines, sometimes fails when pasted into SAS.

Re: Finding the variable causing duplication in my dataset

ScottBass — Sat, 17 Oct 2020 07:11:31 GMT

@jimbobob wrote:

I have a dataset that is supposed to be unique by my variable called arrangement_key, however on occasion its not unique, that's easy to identify the records causing duplication, but scanning all the variables in those duplicate records trying to find the culprit variable(s) is a pain, my dataset has 500 columns, is there an easy way to spit out a dataset that contains the culprit variable(s) that are causing the duplication? thank you in advance for your help.

You don't say what process is causing the duplicate rows. Is it a single table you're concerned about or the result of an SQL join (undesired Cartesian Product) across two or more tables? If you KNOW that your table has non-unique keys, and is participating in a join, you should address this first.

Here is SQL code that will identify duplicate keys, although it is functionally equivalent to SAS code previously posted:

proc sql;
   create table want as
   select key1, key2, key3, count(0) as count
   from have
   group by key1, key2, key3
   having count(0) > 1;
quit;

You can filter on the count column, looking for rows with > 2, > 3, > 4, etc. duplicates.

At this point I usually just "eyeball" the data, scrolling right and skimming for satellite (non-key) columns where the data changes.

But, again, your first step is to ensure your source table keys are, in fact, keys. https://www.google.com/search?q=what+is+a+key+column

Re: How to suppress macro resolution

ScottBass — Wed, 14 Oct 2020 22:29:46 GMT

Thanks Mike. I also realized after posting that my code doesn't account for a null &asics parameter, and would generate a syntactically incorrect where clause (my coding style changes).

Since you have a solution, I don't consider it worthwhile to modify my post.

Glad you got it working.

Re: How to suppress macro resolution

ScottBass — Wed, 14 Oct 2020 21:48:06 GMT

So it looks like you've got a macro variable/parameter containing a list of where clause predicate suffixes, i.e. TZ T1 etc.

And you're trying to build a where clause, looping over those tokens:

cics like 'P%TZ' or cics like 'P%T1'

My macro library is located at https://github.com/scottbass/SAS/tree/master/Macro.

You want %loop, %squote, and %parmv. If you don't want to use them, then use the code as a guide for your own macro.

I would pull the AND operator out of the macro, so the macro contains just the loop construct, with the calling code containing the rest of the where clause syntax. But that is just coding style.

Finally, the simplest approach is just to temporarily set option nomerror to suppress warnings of unresolved macro references. Of course, don't create %TZ or %T1 macros 😉

The code then becomes:

options mprint nomerror;

%macro code;
%if (&__iter__ gt 1) %then or;
cics like %squote(P%&word)
%mend;

%let asics=TZ T1;

data foo;
   set sashelp.class (rename=(name=cics));
   where 1=1 and (%loop(&asics));
run;

* Alternative approach ;
%let where=1=1 and (%loop(&asics));

data foo;
   set sashelp.class (rename=(name=cics));
   where &where;
run;

and the log:

29         %macro code;
30         %if (&__iter__ gt 1) %then or;
31         cics like %squote(P%&word)
32         %mend;
33         
34         %let asics=TZ T1;
35         
36         data foo;
37            set sashelp.class (rename=(name=cics));
38            where 1=1 and (%loop(&asics));
MPRINT(CODE):   cics like
MPRINT(SQUOTE):   'P%TZ'
MPRINT(CODE):   or cics like
MPRINT(SQUOTE):   'P%T1'
39         run;

NOTE: There were 0 observations read from the data set SASHELP.CLASS.
      WHERE (1=1) and (cics like 'P%TZ' or cics like 'P%T1');
2 The SAS System                                                                        08:18 Thursday, October 15, 2020

NOTE: The data set WORK.FOO has 0 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              269.75k
      OS Memory           13092.00k
      Timestamp           15/10/2020 08:42:10 AM
      

40         
41         * Alternative approach ;
42         %let where=1=1 and (%loop(&asics));
43         
44         data foo;
45            set sashelp.class (rename=(name=cics));
46            where &where;
47         run;

NOTE: There were 0 observations read from the data set SASHELP.CLASS.
      WHERE (1=1) and (cics like 'P%TZ' or cics like 'P%T1');
NOTE: The data set WORK.FOO has 0 observations and 5 variables.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      user cpu time       0.00 seconds
      system cpu time     0.00 seconds
      memory              269.62k
      OS Memory           13092.00k
      Timestamp           15/10/2020 08:42:10 AM

Of course, this returns nothing using sashelp.class, but should be syntactically correct for your scenario.

If this does not meet your needs, please give details why so we can help further.

Re: Need alternative of "Filename" statement with Pipe operator for SAS University Edition

ScottBass — Wed, 14 Oct 2020 05:42:22 GMT

Yet another approach...

https://github.com/scottbass/SAS/blob/master/Macro/dirlist.sas

Re: Splitning one variable to multiple variables

ScottBass — Mon, 12 Oct 2020 04:23:07 GMT

1. Format your SAS code using the "SAS Code" icon.

2. When I paste your code into SAS, the first data step doesn't give the correct results. The second data step fails.

BZZZZZZZZZZZZZZZZZZZZZZZ! Try again.

P.S.: Use the SCAN function and PROC TRANSPOSE (or an array - TRANSPOSE would be more flexible). Read the doc for more details.