Searching from a list as a condition in proc freq?

[FunWithBIOS]

Hi SAS community! Finally going to bug you with a question, now that I've been learning from you Super Users for months. (You are an amazing resource -- I can't figure out how much time you're spending on all this or why, but I really admire what you do here and hope to get to your level!)

This may be more of a programming logic question, unless there's a function I'm not aware of that would accomplish this task. 

I have given SAS a list of objects and stored it in a macro list which now looks like this: 

%put &ObjectList; 

"Object1" "Object2" "Object3" "Object4" "" ""

Now I want to get fancy and put it in a condition for proc freq: 

proc freq data=tatertots; tables var1*var2; where propcase(strip(var1)) in(&ObjectList.) and var1 ne ""; run;

In my head, this made perfect sense when I wrote it, but the data looked suspicious. Then I realized, my var1 contains observations which have multiple objects, such as this one: 

Obs       Var1

1         object1, OBJECT4, Object3

Clearly I was asking SAS so search the entirety of propcase(strip(var1)) within &ObjectList, so when there was no match for that entire string it skipped those observations.

I can probably solve this by parsing out var1 to be multiple variables with one object each and adding those conditions to my proc freq. I've had luck doing this with a new dataset via arrays and do loops in the past. I've been practicing using index, scan, substr and some basic character modifying functions, but as far as I know I'll have the same problem with those. 

I guess I want to know what a seasoned, efficient programmer would do because I have the feeling there's something I don't know that doesn't require making a whole new dataset. What would you do? 

Also, in your answer, would you mind including your favorite resource for learning to write macros? Or perhaps share how you learned? I'm really wanting to get better at that and I want a good teacher. 

Thanks again for everything you do, and hope my question makes sense and falls within community guidelines. 

[ballardw]

---

@FunWithBIOS wrote:

Hi SAS community! Finally going to bug you with a question, now that I've been learning from you Super Users for months. (You are an amazing resource -- I can't figure out how much time you're spending on all this or why, but I really admire what you do here and hope to get to your level!)

This may be more of a programming logic question, unless there's a function I'm not aware of that would accomplish this task. 

I have given SAS a list of objects and stored it in a macro list which now looks like this: 

%put &ObjectList; 

"Object1" "Object2" "Object3" "Object4" "" ""

Now I want to get fancy and put it in a condition for proc freq: 

proc freq data=tatertots; tables var1*var2; where propcase(strip(var1)) in(&ObjectList.) and var1 ne ""; run;

In my head, this made perfect sense when I wrote it, but the data looked suspicious. Then I realized, my var1 contains observations which have multiple objects, such as this one: 

Obs       Var1

1         object1, OBJECT4, Object3

Clearly I was asking SAS so search the entirety of propcase(strip(var1)) within &ObjectList, so when there was no match for that entire string it skipped those observations.

I can probably solve this by parsing out var1 to be multiple variables with one object each and adding those conditions to my proc freq. I've had luck doing this with a new dataset via arrays and do loops in the past. I've been practicing using index, scan, substr and some basic character modifying functions, but as far as I know I'll have the same problem with those. 

I guess I want to know what a seasoned, efficient programmer would do because I have the feeling there's something I don't know that doesn't require making a whole new dataset. What would you do? 

Also, in your answer, would you mind including your favorite resource for learning to write macros? Or perhaps share how you learned? I'm really wanting to get better at that and I want a good teacher. 

Thanks again for everything you do, and hope my question makes sense and falls within community guidelines. 

---

If I understand what you want to do is to parse the possibly multiple values held in VAR1 to search for membership in the list held by &OBJECTLIST.

A where statement will not do what you want with any simplicity.

You would likely be better of with a data step before Proc Freq to select the appropriate records first.

Are you actually including blank values in your object list. I would generally consider that to be very confusing and minimum and likely to cause other problems elsewhere at worst.

This may get you started but you have to decide whether the last example goes into your proc freq or not.

If not then you have additional logic to describe and develop.

data example;
   
   input var1 $ 1-25 ;
   do i=1 to countw(var1);
      if propcase(scan(var1,i)) in (&objectlist.) then do;
          output;
          leave;
      end;
   end;
   drop i;
datalines;
object1, object2
Object5
OBJECT2, Object1
Object2, object3
OBJECT5, Object1
;
run;

[FunWithBIOS]

Thanks, Ballard, for your response! 

So I actually have this done previously in a data step for another purpose -- not including my macro list, though:

array split_var1(*) object1-object4  _char_;

i=1;

do while(scan(var1,i,",")ne" ");

split_var1(i)=scan(var1,i,",");

i=i+1;

end;

After I posted, I was able to get the proc freq to output a table with the correct amounts using the dataset which included my code above. My question was more about improvement, I think. And your answer, I believe, is that this is the best way! That answers my question, then. 

Also as a new programmer who doesn't have a big network of programmers to compare notes with, I loved your approach to that array and I think I'll use it. Even "leave" is a new statement for me. Thanks!

Edit: Also, don't worry about the blanks. That has to do with a length I specified earlier that I haven't changed yet. I agree, and it will be fixed.

[ballardw]

---

@FunWithBIOS wrote:

Thanks, Ballard, for your response! 

So I actually have this done previously in a data step for another purpose -- not including my macro list, though:

array split_var1(*) object1-object4  _char_;

i=1;

do while(scan(var1,i,",")ne" ");

split_var1(i)=scan(var1,i,",");

i=i+1;

end;

After I posted, I was able to get the proc freq to output a table with the correct amounts using the dataset which included my code above. My question was more about improvement, I think. And your answer, I believe, is that this is the best way! That answers my question, then. 

Also as a new programmer who doesn't have a big network of programmers to compare notes with, I loved your approach to that array and I think I'll use it. Even "leave" is a new statement for me. Thanks!

Edit: Also, don't worry about the blanks. That has to do with a length I specified earlier that I haven't changed yet. I agree, and it will be fixed.

---

The main thing in this case is that while a WHERE statement can accept uses of functions on values it cannot do "loops" inside the statement and if you need to compare multiple pieces of a variable then the limits of WHERE just don't allow them.

Another approach could have been reshaping the data to split the VAR1 apart and create multiple records. But that likely would have a different frequency count then you wanted though that could be addressed as well.

It would help with questions to be able to state such things as "I want to count the record if at least one of the pieces of VAR1 are in the List" or "all of the pieces of var1 have to be in the list somewhere" (at least two of the possible concerns with your "problem" records that didn't match). Note the later one requires considerably more coding. An exercise for the interested reader would be to modify the code I provided to get the other result. Hint: Leave is still helpful but quit when the current item is not in the list without output.

[Reeza]

You may also want to look into WHICHC() which may help here. Not sure though.

[Reeza]

Avoid macros at all costs is my motto. They do have a purpose and are incredibly useful, but there are often a lot of ways to get things done without macros. For your example one approach that may work is loading the lookup data into a temporary array and doing comparisons. If you want to get really good with SAS, understand the data step or see what @novinosrin recommends. He's relatively new and has progressed incredibly fast IMO. I'm older and have about 15 years of experience. I also have a really good memory....or so I've been told. It's definitely going these days and a lot of things have changed from when I initially learned them, so I have to make a concentrated effort to keep up 🙂 Use it or lose it.

EDIT: update because I accidentally got novinosrin's tag name wrong

[FunWithBIOS]

Wow, that is not an answer I was expecting, but point definitely taken! Thanks Reeza!

[novinosrin]

Thank you mam @Reeza  for the kind words. .I am lucky and privileged to receive favors from you, @art297 (IW workbook share -changed everything - the foremost help ) ,MarkKeintz, @FreelanceReinh PG, @ballardw , John king et all sirsss in plural and lately on a personal level special attention offline by Guru Paul D who is taking a lot of interest in me in getting me up to speed. 

Indeed, with 5 years and to have gotten to this level is very pleasing. I can't thank all the above mentioned enough. My parents never imagined their only child who is spoiled rotten will ever change. My mom a retired forex analyst often follows all the fun here and is well aware of the top contributors. Well it's a sweet symphony so to speak. I dedicate all the good stuff to mom and dad.

Special gratitude from mom,dad and me goes to you for the below link  when you spotted me. That gave me lot of confidence. 

https://communities.sas.com/t5/SAS-Programming/total-visits-and-average-score-in-a-datastep/td-p/411...

 Reeza

Super User

Posts: 25,097

Re: total visits and average score in a datastep

 [ Edited ] 

Options

Posted in reply to novinosrin

‎11-09-2017 04:17 PM - edited ‎11-09-2017 04:17 PM

  This is just for fun. I wanted to experiment how a hash alternative would work at my college lab this afternoon.

@novinosrin If there was any uncertainty about your 'geek' status or SAS expertise consider it certain now 

[novinosrin]

  This is just for fun. I wanted to experiment how a hash alternative would work at my college lab this afternoon.

@novinosrin If there was any uncertainty about your 'geek' status or SAS expertise consider it certain now 

[FreelanceReinh]

---

@FunWithBIOS wrote:

(...) my var1 contains observations which have multiple objects, such as this one: 

Obs       Var1

1         object1, OBJECT4, Object3

---

Hi @FunWithBIOS,

From my experience it's rarely a good idea to store "multiple objects" in a single character variable, except for reporting purposes. The difficulties you encountered are a case in point. I think you'll make your life easier if you store lists of values in separate observations. Then you have a variety of techniques at your disposal to use them. In a DATA step you can perform BY-group processing (e.g. in the form of so called DOW loops for data aggregation). Also the (more advanced) hash object offers a lot of possibilities.

PROC SQL is great for list processing as well. For example, if there are several observations for one ID with distinct VAR1 values, you can select IDs based on any conceivable criteria regarding the relationship between this set of VAR1 values and your &ObjectList, e.g.

group by id
having min(propcase(var1) in (&ObjectList));

for the criterion "the VAR1 values (case insensitive) form a subset of the values in &ObjectList". You can even replace &ObjectList with a subquery like select obj from list, which allows for more conditions than just IN (namely EXISTS, ANY and ALL). Note that the non-zero counts resulting from your PROC FREQ step could also be obtained easily by PROC SQL (count(*) in conjunction with group by var1, var2).

---

@FunWithBIOS wrote:

Also, in your answer, would you mind including your favorite resource for learning to write macros? Or perhaps share how you learned?

---

This is a bit difficult for me because I learned the basics of SAS and also of macro programming primarily from the printed SAS 6 documentation in the late 1990s. I own about 15 books on SAS topics, but none about macro language. A good resource that I use occasionally is the digital legacy of Roland Rashleigh-Berry (a former colleague of mine). See in particular Roland's SAS tips and techniques (including Tips on writing SAS macros) and his SAS macros.

I assume that Carpenter's Complete Guide to the SAS® Macro Language is great because I found two of the author's other books very useful (Carpenter's Guide to Innovative SAS® Techniques and Carpenter's Complete Guide to the SAS® REPORT Procedure).

That said, the importance of macros seems to be overestimated by many posters here. As a consequence, the archives of this forum contain tons of reports of problems arising from inappropriate or unnecessary use of macro language.

[ballardw]

---

@FunWithBIOS wrote:

Also, in your answer, would you mind including your favorite resource for learning to write macros? Or perhaps share how you learned? I'm really wanting to get better at that and I want a good teacher. 

---

Besides the documentation one of the first places I would look is in your SAS install. Many of the SAS components have some macro library and you can chase them down from the configuration file, the output from Proc Options or searching for folders named SASMACRO in the programs folder for SAS.

CAUTION: some of the DMS modification macros may not run a bits of the DMS have been deprecated or changed. But there a many SAS written macros of various lengths and complexities to examine and steal code from...

[Reeza]

Oh...and the documentation has a decent set of examples that cover common usage.

https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n02xowj8yuqfo4n0zzi98shu8qup.htm&docse...

I believe a SAS Studio Task was also developed to help with macros:

https://communities.sas.com/t5/SAS-Communities-Library/SAS-Studio-Custom-Task-Tuesday-How-to-Create-...