About ScottBass

ScottBass · ‎07-18-2019

I'm sure there are data step approaches but I work with RDBMS's so much that I often think in SQL. If your real datasets are huge this might not perform well. Hope this helps... * source data ; data have; input id amt; datalines; 1 5 1 5 1 106 2 90 2 100 3 111 3 111 3 111 3 111 4 837 4 838 4 839 ; run; * create two keys - this could be a view if your data is large ; data have; format sk gk; sk+1; set have; by id; if first.id then gk=0; gk+1; run; /*01 after the first two occurrences, the difference is greater than 20% compared to the third one*/ * this is unclear but here is a start ; proc sql _method; * for debugging ; create table want1_debug as select a.id, a.amt, b.amt as amt_3 from have a left join have b on a.id=b.id and b.gk=3 order by a.sk; * for real ; create table want1 as select distinct a.id from have a left join have b on a.id=b.id and b.gk=3 where b.amt > a.amt*1.2; quit; /*02 where the amt field is constant*/ proc sql _method; * for debugging ; create table want2_debug as select id,min(amt) as min_amt,max(amt) as max_amt from have group by id; * for real ; create table want2 as select distinct id from ( select id,min(amt) as min_amt,max(amt) as max_amt from have group by id ) x where x.min_amt = x.max_amt; quit; /*03 where the amt field always changeing*/ proc sql _method; * for debugging ; create table want3_debug as select a.id,a.amt,b.amt as next_amt,(a.amt ^= b.amt or b.amt is missing) as flag from have a left join have b on a.id=b.id and a.sk+1=b.sk order by a.sk; * for real ; create table want3 as select distinct id from ( select id,min(flag) as min_flag from ( select a.id,a.amt,b.amt as next_amt,(a.amt ^= b.amt or b.amt is missing) as flag from have a left join have b on a.id=b.id and a.sk+1=b.sk ) x group by id having min_flag ^= 0 ) x; quit;

ScottBass · ‎07-17-2019

Review this and tell us which observation numbers are incorrect: data have; do time="00:00:00"t to "23:59:59"t; output; end; format time time.; run; proc format; value timerng "06:00:00"t -< "16:00:00"t = "6:00am - 3:59pm: Daytime" "16:00:00"t -< "22:00:00"t = "4:00pm - 9:59pm: Evening" "22:00:00"t - "23:59:59"t, "00:00:00"t -< "06:00:00"t = "10:00pm - 5:59am: Overnight"; run; data want; set have; time2=put(time,timerng.); run;

ScottBass · ‎07-17-2019

You need to provide more information, specifically the code you ran, the log and what isn't working. What part of this is unclear?

ScottBass · ‎07-17-2019

Format your posts as a self-contained data step using datalines statements. Revisit the documentation on the round function. Does this give you what you want? data have; length Decimal Data 8; input Decimal Data; datalines; 0 1.123 1 2.345 2 3.456 3 4.567 ; run; data want; set have; newdata=round(Data,10**(-Decimal)); format newdata best.; run; Output: Decimal Data newdata 0 1.123 1 1 2.345 2.3 2 3.456 3.46 3 4.567 4.567

ScottBass · ‎07-17-2019

@Reeza wrote: It's not intended to be mean, and I hope it doesn't come across that way, just that it's such a vague statement that conveys no information. No it didn't come across as mean to me, and I hope not to @lydiawawa as well. I just thought your examples of vagueness, such as the computer disintegrating to bits, as funny. Perhaps that's just my sense of humor. I wonder how you'd post an example of that? Take a picture and attach it I suppose 😉

ScottBass · ‎07-17-2019

Simplify and ensure that SAS and Python MD5 functions return the same results (AFAIK they should but you seem to be wanting to verify that): data test; set sashelp.class (keep=name); hash1=md5(name); hash2=md5(strip(name)); hash1x=put(hash1,hex32.); hash2x=put(hash2,hex32.); run; Also note that SAS variables are padded with spaces to the length of the variable. Note the difference between md5(var) vs. md5(strip(var)). Get SAS and Python to generate the same MD5 result for "Alfred", "Alice", "Bob", etc, then add complexity (concatenated columns) to the mix. I don't know if this will help with your export issues? https://github.com/scottbass/SAS/blob/master/Macro/export.sas

ScottBass · ‎07-17-2019

@Reeza wrote: Didn't work is vague and could mean anything from doesn't tell us if your computer disintegrated into bits or if you forgot a semicolon. You need to provide more information, specifically the code you ran, the log and what isn't working. Classic reply @Reeza 🙂 🙂 🙂 Couldn't stop laughing. Plus it's spot on (but still funny). Wish I could give this 10 likes.

ScottBass · ‎07-17-2019

SAS Global Forum Presenter Mentoring Program Why should I consider presenting a paper at SAS Global Forum? First and foremost, SAS Global Forum is a SAS user’s conference. You, the SAS end user, are the main source of content for the conference. Presenting a paper is a way for you to share your SAS knowledge and give back to the SAS community. Presenting a paper is also a great way for you to stand above the crowd. For you to present a paper, you were accepted in a competitive process to share your knowledge and ideas. Furthermore, your paper will “live on” for decades via the online SAS Global Forum Conference Proceedings. This is a great way to differentiate yourself from your SAS peers, and could even be the difference in you getting an interview for that job you’ve always wanted. For example, you could provide the link to your paper in the online Conference Proceedings in your LinkedIn profile as a publication, and stand out from your peers on LinkedIn. I think my work is pretty cool, but I have no idea where to begin This is where the Presenter Mentoring program will help. The SAS Global Forum Presenter Mentors are seasoned SAS Professionals who are very experienced at presenting at SAS Global Forum. We will help you consolidate and refine your ideas, create your paper abstract, help with your paper title, give feedback on your presentation slide deck, and coach you with your presentation. If you are doing cool things in SAS, and are proud of your work, your fellow SAS peers would love to learn from you! But I HATE public speaking! Guess what? Many people in our industry do! I certainly am one of them. But, I forced myself to present at my local SAS User Groups, and eventually at SAS Global Forum, and I got better and better with every presentation I gave. You really will have a sympathetic audience – your fellow SAS professionals who are keen to learn what you know. After all, they chose your presentation to attend! Furthermore, the ability to effectively present your ideas is a very valuable skill, in IT or indeed any industry. It really is a great opportunity for professional development by presenting your paper at SAS Global Forum. And we, the Presenter Mentoring Team, will help you through the entire process, including rehearsing your presentation. Finally, if your work is cool but you just aren’t ready to present, consider a co-presenter to do the bulk or entirety of your presentation. You, as the subject matter expert, can be there to field questions from the audience after the presentation. Or, you can submit your abstract as an e-poster, where you present your ideas in an informal setting, and just field casual one-on-one questions from attendees that attend your e-poster. I'm sure there are many times in life that you've faced your fears, did it anyway, and grew through that process. Here is another chance for personal growth. So, how does the process work? In August SAS will open the SAS Global Forum Call For Papers website. A simple Google search “SAS Global Forum 2020 Call For Papers” will find the website. Here is the direct link: Call For Content After creating an account with your details, you will enter your presentation title, abstract, and working outline for consideration by the SAS Global Forum Abstract Review Team. How are abstracts chosen? Your abstract is very important! Think of it like your resume or CV: a sloppy, poorly written, misspelled abstract and/or working outline leaves a bad impression, no matter how great you and your ideas are. I, the reviewer, think I’ll just put this abstract in the bin and pick this shiny, well-written abstract from this other person. Even if your ideas are great and your code is brilliant, your abstract and working outline are key to making a good impression that your paper and presentation will be of high quality. Your abstract and working outline should clearly state what your paper and presentation will say. Also, your abstract is what is published in the SAS Global Forum agenda, and tells the attendees what your presentation is all about. Make me, the Abstract Reviewer, want to attend your paper, instead of the other papers being presented at the same time and on a similar topic as yours. Put effort into your abstract. Spell check your abstract. If English is not your native language, consider getting a colleague or the Presenter Mentoring team to review your abstract for grammar, spelling, and that it’s clear, compelling, “tells a story”, and makes me want to attend your paper. As part of the abstract submission process, you can also attach a draft (or final) version of your paper. However, consider the timing of events: the abstracts are due by 30 September 2019, while the papers are due by 27 February 2020. If you have a good quality paper that is finished, for example if you’ve presented this paper at a local or regional SAS User Group, then by all means attach your paper if it helps convince the Abstract Review Team to accept your abstract. But, don’t just attach a sloppy, misspelled, incomplete paper to your submission, as this will detract from your abstract and the overall submission. Instead, put more effort into your abstract and working outline. You can write that fantastic paper once your abstract has been accepted. Attaching a paper is optional to the submission process. The abstract submission process is competitive. There are always more abstracts submitted than there are rooms and times available for presentations. A panel comprised of the SAS Global Forum committee team and select seasoned SAS professionals form the Abstract Review Team. Abstracts are reviewed by members of this team, and are graded on a scale of 0 to 5. The grades are averaged across the reviewers to derive the overall abstract score. The abstracts are then sorted by score, and a cut is made based on the room allocations for that subject area (i.e. analytics, programming, administration, etc.) I’m sold! What now? If you know that you would like help in gathering your thoughts, coming up with an idea, and creating an abstract, then click the link below now, so we can assign you a mentor as early in the process as possible. Please provide as much information as possible, and at least your subject area (analytics, programming, administration, etc.), so we can assign an appropriate mentor to you. Request a Mentor Otherwise, look for the SAS Global Forum 2020 Call For Papers website in August.

ScottBass · ‎07-14-2019

@bill73 wrote: Download the attachment table, please! No. Supply your SAS log, please! (And what "attachment table" are you talking about? A .sas program? An Excel file?) The problem isn't with the debugger, the problem is with your SAS program. My guess is you're missing a semi-colon, and SAS gets confused about the run statement. IF _N_ = 1 THEN DO; SET SWITCHES; END; /* IF _N_ = 1 */ This statement was redundant, because the dataset “work.switches” contained only 1 sentence. So we reduce this Statement to: SET SWITCHES; Your thinking is incorrect. The original code will read a single row from SET SWITCHES, then retain those variables for the life of the data step, or until explicitly changed. Your code change will mean a record is read from SWITCHES for each iteration of the data step. Since SAS will end the data step on the first data set that has read past the end of data, that means your data step will only run for a single iteration. I suspect you have multiple SET statements in your code, but I can't know for sure since you haven't supplied your SAS log.

ScottBass · ‎07-12-2019

Well this looked kind of fun 🙂 Here's my take on it. Perhaps someone can suggest performance improvements but it has acceptable performance on SASHELP. %code1 performs better but can miss hits if you're searching for multiple strings. I'll leave it to you to finish "macro-izing" it, such as wrapping it in a top level macro, case-insensitive search, etc. Go here to download the macros: https://github.com/scottbass/SAS/tree/master/Macro * list of datasets ; proc sql noprint; select catx('.',libname,memname) into :datasets separated by ' ' from dictionary.tables where libname="SASHELP" and memtype='DATA' ; quit; %put &datasets; * macro to process the datasets ; %macro code1; data temp; length _dataset_ $32 _variable_ $32 _value_ $200 _dsname_ $41; set &word indsname=dsname; array vars{*} _character_; do i=1 to dim(vars); if vars{i} in (%seplist(&value,indlm=^,nest=q)) then do; _dataset_=dsname; _variable_=vname(vars{i}); _value_=vars{i}; output; stop; end; end; keep _dataset_ _variable_ _value_; run; proc append base=results1 data=temp (rename=(_dataset_=dataset _variable_=variable _value_=value)) ; run; %mend; * Alternative, slower but more complete ; %macro code2; data temp; length _dataset_ $32 _variable_ $32 _value_ $200 _dsname_ $41; set &word indsname=dsname; array vars{*} _character_; do i=1 to dim(vars); if vars{i} in (%seplist(&value,indlm=^,nest=q)) then do; _dataset_=dsname; _variable_=vname(vars{i}); _value_=vars{i}; output; * stop; end; end; keep _dataset_ _variable_ _value_; run; proc sort noduprecs; by _all_; run; proc append base=results2 data=temp (rename=(_dataset_=dataset _variable_=variable _value_=value)) ; run; %mend; %kill(data=results1 results2); %let value=Ketchikan^New York^Alice^IBM; * testing ; /*%loop(sashelp.zipcode,mname=code1)*/ * the whole shebang ; * note this misses some hits in zipcode because the stop statement stops on the first hit ; %loop(&datasets,mname=code1) * alternative approach ; %loop(&datasets,mname=code2)

ScottBass · ‎07-10-2019

Edit your post, pasting the code using the Insert SAS Code icon.

ScottBass · ‎07-10-2019

@hashman wrote: @ScottBass : Neat! I like your FINDC approach better, but actually both are better than the SUBSTR pseudo-function known to be notoriously slow. Yeah, I like the FINDC approach better myself. I started with FINDW, mistakenly thought FINDC didn't have the start position parameter, hit the doc, then decided to post both approaches. What would be nice is a parameter to "find the Nth occurrence" of [character|word] in FINDC | FINDW. That's where @ChrisNZ 's solution is cool, but I doubt most folks would come up with that (but then Chris isn't "most folks" 😉 ). I suspect the OP will have to hit the Perl Regex docs to understand that (really cool) solution.

ScottBass · ‎07-10-2019

Yet another approach: DATA WORK.HAVE; a='a, b, cd, ef, gdj'; pos=0; do i=1 to 3; pos=findw(a,',',pos+1,',','K'); end; drop i; RUN; findw finds "words" The 1st argument is the string to search. The 2nd argument defines the "word" to find (a comma). The 3rd argument defines the starting position for the search. Needed to loop and find the 3rd comma. The 4th argument defines the delimiters that separate words. In this case the delimiter is also the word we're searching for. The 5th argument "keeps" the delimiter, i.e. reverses the meaning. A comma is now not treated as a delimiter. Another approach (similar to hashman's but without the substr): DATA WORK.HAVE; a='a, b, cd, ef, gdj'; pos=0; do i=1 to 3; pos=findc(a,',',pos+1); end; drop i; RUN; P.S.: What are you really trying to do? I doubt you want the position of the 3rd comma; instead, what do you want to do with that position?

ScottBass · ‎07-09-2019

I don't know vfox so don't know the intricacies of isblank and isnull. Re: your code: I like the missing() function, as it "does the right thing" whether the variable is character or numeric: data test; chr1=''; chr2=' '; chr3=' '; chr4='foo'; num1=.; num2=.a; num3=.z; num4=123; ismiss_chr1=missing(chr1); ismiss_chr2=missing(chr2); ismiss_chr3=missing(chr3); ismiss_chr4=missing(chr4); ismiss_num1=missing(num1); ismiss_num2=missing(num2); ismiss_num3=missing(num3); ismiss_num4=missing(num4); run;

ScottBass · ‎07-09-2019

Use this macro to build your directory list as a SAS dataset: https://github.com/scottbass/SAS/blob/master/Macro/dirlist.sas You may have to post-process that dataset to create a valid SAS dataset name. For example, dataset = scan(basename,2,'_'); Use this macro to loop over that list, calling the child macro %code to create your datasets: https://github.com/scottbass/SAS/blob/master/Macro/loop.sas You create the %code macro to suit your needs, in this case a simple data step. Although PROC IMPORT ... CSV might also work? Read the macro headers for use cases. You may need to download additional utility macros, such as https://github.com/scottbass/SAS/blob/master/Macro/parmv.sas

Online Status	Offline
Date Last Visited	‎03-11-2021 01:34 AM

Re: How do I unsubscribe from a topic?

Re: How do I unsubscribe from a topic?

How do I unsubscribe from a topic?

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

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

Re: How to improve performance in Data step where clause

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

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

Re: using rsubmit in a loop

Re: using rsubmit in a loop

Re: LOCF

Re: Programming Question

Re: Programming Question

Re: SAS query - Split data to different dataset

Re: SAS query - Split data to different dataset

Re: Why the result useing the catx have blanks

Re: Check numeric values in alphanumeric variables

Re: Read a Variable Value into a Macro Variable

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

EG Custom Task to play a sound

How to create a working outline for SAS Global Forum

SAS Global Forum Presenter Mentoring Program

Re: how do i retaining values by id

Re: Format time in grouped blocks

Re: Format time in grouped blocks

Re: Using a number stored in a variable to convert another varibale in...

Re: Format time in grouped blocks

Re: md5 hash issue

Re: Format time in grouped blocks

SAS Global Forum Presenter Mentoring Program

Re: Enterprise Guide 7.15 Data-Step-Debugging

Re: Finding a value in all datasets in all variables - revisited

Re: How to create a string of values with a condition

Re: 3rd position

Re: 3rd position

Re: isblank and isnull fucntion from vfox to sas

Re: How to resolve a macro variable with number at beginning

SAS Global Forum 2020