cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
jpprovost
‎01-30-2020
  • 73 Posts
  • 43 Likes Given
  • 0 Solutions
  • 6 Likes Received

Re: What am I doing wrong with this macro?

by Quartz | Level 8 in SAS Programming
‎01-16-2020 10:16 AM
‎01-16-2020 10:16 AM
@jpprovost wrote: I think the first one (set a macro variable to the list of values and replace the list with a reference to the macro variable) is the one that would work the best for me. I tried to run the code with the modifications proposed by you : %if &region. = "Abitibi-Témiscamingue" %then %do; %let no_comte = (1, 2, 101); %let out=AT; %end; all and it gives me the following error : A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &region. = Abitibi-Témiscamingue. The macro LISTE_REGION will stop executing. I think I may have found the reason of the error. In the name of the region Abitibi-Témiscamingue, there's a " - " which I think SAS interpret as a minus sign. Instead, I tried to fix it with %str() in the macro and also in the parameter and it seems to work. Am I doing it right ? ... View more

Re: What am I doing wrong with this macro?

by Quartz | Level 8 in SAS Programming
‎01-16-2020 10:04 AM
‎01-16-2020 10:04 AM
I think the first one (set a macro variable to the list of values and replace the list with a reference to the macro variable) is the one that would work the best for me. I tried to run the code with the modifications proposed by you : %if &region. = "Abitibi-Témiscamingue" %then %do; %let no_comte = (1, 2, 101); %let out=AT; %end; all and it gives me the following error : A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &region. = Abitibi-Témiscamingue. The macro LISTE_REGION will stop executing. ... View more

Re: What am I doing wrong with this macro?

by Quartz | Level 8 in SAS Programming
‎01-16-2020 09:06 AM
‎01-16-2020 09:06 AM
@Tom wrote: [...]   Were you intending to set some macro variables instead? Something like? %if &region. = "Abitibi-Témiscamingue" %then %do; %let no_comte = (1, 2, 101); %let out=AT; %end; If so then you should probably add either a %LOCAL or a %GLOBAL statement to your macro definition to make it clear whether you intend those macro variable to only live as long as the macro is running or not.   Hi Tom,   Exactly. I didn't know how to "express" myself of what I'm trying to achieve in a SAS-way (my apologies for this). In fact, I want to create a macro that output some filtered (the filter is no_comte) lists based on a specific parameter (here is a region). Then, inside the macro, I want to do :   If Region = "ABC" then no_comte = (1,2,3)   Because I want to use it later on a data step to filter it, like :   data I_want_this; set orginal_lists; where VARIABLE_TO_FILTER in (no_comte). run;   Since the variable_to_filter is numeric, I want the variable no_comte to be a liste in the form (1, 2, 3)... I don't know if I'm clear enough ?   ... View more

What am I doing wrong with this macro?

by Quartz | Level 8 in SAS Programming
‎01-15-2020 11:01 PM
‎01-15-2020 11:01 PM
Hi there,   I want to extract a subset of a dataset based on a specific input variable called "region".   Here's my code, but I don't know why it doesn't works.     %macro liste_region (region=); %if &region. = "Abitibi-Témiscamingue" %then no_comte = "(1, 2, 101)" and out="AT"; FILENAME output "/folders/myfolders/&out..xlsx"; data work.membres_actifs_&out.; set work.membres_actifs_circ; where NoComteResidence in no_comte; run; data work.membres_anciens_&out. ; set work.membres_anciens_circ; where NoComteResidence in no_comte; run; proc export data=work.membres_actifs_&out. outfile=output dbms=xlsx replace; sheet=membres_actifs; run; proc export data=work.membres_anciens_&out. outfile=output dbms=xlsx replace; sheet=membres_anciens; run; %mend liste_region; %liste_region(region="Abitibi-Témiscamingue");       It gives me the following error:   1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; 72 73 %liste_region(region="Abitibi-Témiscamingue"); NOTE: Line generated by the invoked macro "LISTE_REGION". 73 no_comte = "(1, 2, 101)" and out="AT" ________ 180 WARNING: Apparent symbolic reference OUT not resolved. ERROR 180-322: Statement is not valid or it is used out of proper order. WARNING: Apparent symbolic reference OUT not resolved. NOTE: Line generated by the invoked macro "LISTE_REGION". 73 data work.membres_actifs_&out.(drop=ComteMembre Titre _ 22 200 73 ! DateNaissance Ehnie Langue M90 NoComteMembre NoComteResidence NoPLQ P90 P1AN Reg Section 73 ! Anciennete Cat: ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, /, ;, _DATA_, _LAST_, _NULL_. ERROR 200-322: The symbol is not recognized and will be ignored. NOTE: Line generated by the invoked macro "LISTE_REGION". 73 cp_: ind: region_adm); set work.membres_actifs_circ; where NoComteResidence in no_comte; run; ________ 22 76 ERROR: Syntax error while parsing WHERE clause. ERROR 22-322: Syntax error, expecting one of the following: (, :. ERROR 76-322: Syntax error, statement will be ignored. NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.MEMBRES_ACTIFS_ may be incomplete. When this step was stopped there were 0 observations and 18 variables. WARNING: Data set WORK.MEMBRES_ACTIFS_ was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds WARNING: Apparent symbolic reference OUT not resolved. NOTE: Line generated by the invoked macro "LISTE_REGION". 73 data 73 ! work.membres_anciens_&out. (drop=ComteMembre Titre DateNaissance Ehnie Langue M90 NoComteMembre NoComteResidence NoPLQ _ 22 200 73 ! P90 P1AN Reg Section Anciennete ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, (, /, ;, _DATA_, _LAST_, _NULL_. ERROR 200-322: The symbol is not recognized and will be ignored. NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.MEMBRES_ANCIENS_ may be incomplete. When this step was stopped there were 0 observations and 18 variables. WARNING: Data set WORK.MEMBRES_ANCIENS_ was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds NOTE: Line generated by the invoked macro "LISTE_REGION". 73 Cat: cp_: ind: region_adm); set work.membres_actifs_circ; where NoComteResidence in no_comte; run; ________ 22 76 ERROR: Syntax error while parsing WHERE clause. ERROR 22-322: Syntax error, expecting one of the following: (, :. ERROR 76-322: Syntax error, statement will be ignored. ERROR: The value ABITIBI-TÉMISCAMINGUE is not a valid SAS member name. NOTE: The SAS System stopped processing this step because of errors. WARNING: The data set WORK.MEMBRES_ACTIFS_ may be incomplete. When this step was stopped there were 0 observations and 48 variables. WARNING: Data set WORK.MEMBRES_ACTIFS_ was not replaced because this step was stopped. NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.01 seconds WARNING: Apparent symbolic reference OUT not resolved. NOTE 137-205: Line generated by the invoked macro "LISTE_REGION". 73 Cat: cp_: ind: region_adm); set work.membres_actifs_circ; where NoComteResidence in no_comte; run; proc sort 73 ! data=work.membres_actifs_&out. ; by ComteResidence Nom Prenom; run; proc sort data=work.membres_anciens_&out. ; by _ 22 73 ! ComteResidence Nom Prenom; ERROR 22-322: Syntax error, expecting one of the following: ;, (, ASCII, BUFFNO, DANISH, DATA, DATECOPY, DETAILS, DIAG, DUPOUT, EBCDIC, EQUALS, FINNISH, FORCE, IN, ISA, L, LEAVE, LIST, MESSAGE, MSG, NATIONAL, NODUP, NODUPKEY, NODUPKEYS, NODUPLICATE, NODUPLICATES, NODUPREC, NODUPRECS, NODUPS, NOEQUALS, NORWEGIAN, NOTHREADS, NOUNIKEY, NOUNIKEYS, NOUNIQUEKEY, NOUNIQUEKEYS, NOUNIQUEREC, NOUNIQUERECS, NOUNIREC, NOUNIRECS, OSA, OUT, OVERWRITE, PAGESIZE, PRESORTED, PSIZE, REVERSE, SIZE, SORTSEQ, SORTSIZE, SORTWKNO, SWEDISH, T, TAGSORT, TECH, TECHNIQUE, TESTHSI, THREADS, UNIOUT, UNIQUEOUT, WKNO, WORKNO. NOTE: Line generated by the invoked macro "LISTE_REGION". 73 Cat: cp_: ind: region_adm); set work.membres_actifs_circ; where NoComteResidence in no_comte; run; proc sort 73 ! data=work.membres_actifs_&out. ; by ComteResidence Nom Prenom; run; proc sort data=work.membres_anciens_&out. ; by _ 200 73 ! ComteResidence Nom Prenom; ERROR 200-322: The symbol is not recognized and will be ignored. WARNING: Apparent symbolic reference OUT not resolved. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds WARNING: Apparent symbolic reference OUT not resolved. 22: LINE and COLUMN cannot be determined. NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred. ERROR 22-322: Syntax error, expecting one of the following: ;, (, ASCII, BUFFNO, DANISH, DATA, DATECOPY, DETAILS, DIAG, DUPOUT, EBCDIC, EQUALS, FINNISH, FORCE, IN, ISA, L, LEAVE, LIST, MESSAGE, MSG, NATIONAL, NODUP, NODUPKEY, NODUPKEYS, NODUPLICATE, NODUPLICATES, NODUPREC, NODUPRECS, NODUPS, NOEQUALS, NORWEGIAN, NOTHREADS, NOUNIKEY, NOUNIKEYS, NOUNIQUEKEY, NOUNIQUEKEYS, NOUNIQUEREC, NOUNIQUERECS, NOUNIREC, NOUNIRECS, OSA, OUT, OVERWRITE, PAGESIZE, PRESORTED, PSIZE, REVERSE, SIZE, SORTSEQ, SORTSIZE, SORTWKNO, SWEDISH, T, TAGSORT, TECH, TECHNIQUE, TESTHSI, THREADS, UNIOUT, UNIQUEOUT, WKNO, WORKNO. 200: LINE and COLUMN cannot be determined. NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred. ERROR 200-322: The symbol is not recognized and will be ignored. WARNING: Apparent symbolic reference OUT not resolved. NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds cpu time 0.01 seconds WARNING: Apparent symbolic reference OUT not resolved. NOTE 137-205: Line generated by the invoked macro "LISTE_REGION". 73 run; proc export data=work.membres_actifs_&out. outfile=output dbms=xlsx replace; sheet=membres_actifs; run; proc _ 22 73 ! export data=work.membres_anciens_&out. outfile=output dbms=xlsx replace; sheet=membres_anciens; run; ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATA, DBLABEL, DBMS, DEBUG, FILE, LABEL, OUTFILE, OUTTABLE, REPLACE, TABLE, _DEBUG_. NOTE: Line generated by the invoked macro "LISTE_REGION". 73 run; proc export data=work.membres_actifs_&out. outfile=output dbms=xlsx replace; sheet=membres_actifs; run; proc _ 200 73 ! export data=work.membres_anciens_&out. outfile=output dbms=xlsx replace; sheet=membres_anciens; run; ERROR 200-322: The symbol is not recognized and will be ignored. NOTE: PROCEDURE EXPORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds WARNING: Apparent symbolic reference OUT not resolved. NOTE: The SAS System stopped processing this step because of errors. WARNING: Apparent symbolic reference OUT not resolved. 22: LINE and COLUMN cannot be determined. NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred. ERROR 22-322: Syntax error, expecting one of the following: ;, (, DATA, DBLABEL, DBMS, DEBUG, FILE, LABEL, OUTFILE, OUTTABLE, REPLACE, TABLE, _DEBUG_. 200: LINE and COLUMN cannot be determined. NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN where the error has occurred. ERROR 200-322: The symbol is not recognized and will be ignored. NOTE: PROCEDURE EXPORT used (Total process time): real time 0.00 seconds cpu time 0.00 seconds WARNING: Apparent symbolic reference OUT not resolved. NOTE: The SAS System stopped processing this step because of errors. 74 75 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;     ... View more

Re: Extract a subset of a numerical variable

by Quartz | Level 8 in SAS Programming
‎12-02-2019 01:21 PM
‎12-02-2019 01:21 PM
@ErikLund_Jensen wrote: Hi @jpprovost    You can't specify 201910 as a date constant, because a date constant must incude a value for the day as shown here:   44 data _null_; 45 ym = '201910'd; --------- 77 ERROR: Invalid date/time/datetime constant '201910'd. ERROR 77-185: Invalid number conversion on '201910'd. 46 run; NOTE: The SAS System stopped processing this step because of errors. ... 47 48 data _null_; 49 ym = '01oct2019'd; 50 run; NOTE: DATA statement used (Total process time): real time 0.00 seconds if you have 201910 as a string or want to specify it as a constant, it is necessary to use an input function with the informat yymmn6. The format yymmn6. works both as informat and format, so you can go from the string 201910 to a SAS date value representing 01oct2019 and from the SAS date value back to the string 201910 as shown here:   data _null_; format ym ddmmyyd10.; ym = input('201910',yymmn6.); put ym=; str_ym = put(ym,yymmn6.); put str_ym=; run; The result is:   ym=01-10-2019 str_ym=201910   regards Erik       Ok, I understand. So if I use ym = '01OCT2019'd is there a way to extract the string "OCT2019" from the variable? ... View more

Re: Extract a subset of a numerical variable

by Quartz | Level 8 in SAS Programming
‎12-02-2019 10:09 AM
‎12-02-2019 10:09 AM
Every solution seems to work, I will accept yours because the &ym macro is effectively a month-year variable.   I have a subquestion.   Let's say that : ym = '201910'd   Is there a way to extract the string "201910" from this variable with this particular format? ... View more

Extract a subset of a numerical variable

by Quartz | Level 8 in SAS Programming
‎11-29-2019 03:49 PM
‎11-29-2019 03:49 PM
Hi there,   I am struggling (and I don't know why) to extract the first 4 digits and the last two digits of a variable.   Here's the variable :   %let ym = 201910; I tried this command and this is not working. I don't know what I do wrong...   data _null_; test1 = substr(&ym., 1, 4); test2 = substr(&ym., 4, 2); run;   Maybe it's because we're Friday...   Any help would be very appreciated.   Thanks, ... View more

Re: How to speed up a datastep procedure?

by Quartz | Level 8 in SAS Programming
‎11-13-2019 01:52 PM
‎11-13-2019 01:52 PM
@Tom wrote: You told Oracle you didn't want SOURCE, so you cannot use it in the WHERE clause. Hint: SAS will process the dataset options in alphabetical order, WHERE is the last one. It works, my bad. Thanks to all. Very useful as always. ... View more

Re: How to speed up a datastep procedure?

by Quartz | Level 8 in SAS Programming
‎11-13-2019 11:17 AM
‎11-13-2019 11:17 AM
@VDD wrote: @jpprovost maybe try doing the processing into more than one datastep.  Fetch data => work data => put results.   combined those states where the tax is the same.   Use Proc SQL with case conditions.   Hi @VDD ,   I tried with a PROC SQL and the CASE conditions. Wow, really fast (2 min vs 17 min). Really good improvement.   @Tom ,   I tried to combine my WHERE and my KEEP into one line and it says that one of my variable (SOURCE) is not on file?   Here's the line :   set myset(keep = field1 field2 field3 field4 field5 field6 ... field14 where=(source="XYZ" and effective_date <= &MY. and expired_date >= &MY.)); I got the following error:   ERROR: Variable SOURCE is not on file myset. ... View more

Re: How to speed up a datastep procedure?

by Quartz | Level 8 in SAS Programming
‎11-13-2019 10:33 AM
‎11-13-2019 10:33 AM
@ballardw wrote: One place: if put(effective_date, yymmn.) <= &MY. and put(expired_date,yymmn.) >= &MY.; Instead of doing 2 numeric to text and then comparing, and possibly I would even guess a further text to numeric conversion create an actual date value for &MY and you could add to the where clause   and( effective_date le &my. and expired_date ge &my.) If you see a NOTE about character to numeric conversions then you are wasting cycles with this.   Another though very minor is this set of If/then/else statements. if state in ("BC") then tx = 0.02; else if state in ("NT") then tx = 0.03; else if state in ("YK") then tx = 0.02; An INFORMAT when you are assigning values based on a single input variable, especially if it does not change often can execute much quicker: Proc format library=work; invalue sttx 'BC' = 0.02 'NT' = 0.03 'YK' = 0.02 other=_error_ ; run; data example; input st $; tx = input(st,sttx.); datalines; BC NT YK QQ ; The option on the invalue of other=_error_ treats any unexpected value as an error, which your If/Then/Else approach may not catch (since you didn't show the entire block);     I saw a note about conversion, you are right. Will try to use your advice. However, I would like to passe the condition on thoses dates (expired and effective) into the WHERE in the input dataset line. How could I do that if I already have one condition (which is the SOURCE =...)?   ... View more

Re: How to speed up a datastep procedure?

by Quartz | Level 8 in SAS Programming
‎11-13-2019 10:25 AM
‎11-13-2019 10:25 AM
@Tom wrote: You will need to tell ORACLE what columns you want to read it with the KEEP= dataset option.  You might still need a KEEP, or more likely a DROP statement, to tell SAS what variables from the data step to write out.  Some people code a KEEP= dataset option on the output dataset name, but I always find that as a confusing way to code.   You could also perhaps increase the speed a lot by doing the processing in Oracle. Especially if you want to eliminate observations based on some of your calculated variables.  Or even more so if you want to just summarize the data instead of getting all of the detail lines.  But then you need to know how to code in Oracle's flavor of SQL.   I will try the "KEEP=" options in the input dataset. By the mean time, I tried to re-run this code with those options :   options threads cpucount=actual; options msglevel=i fullstimer; And here's the ouput in the log. Is there something you see that could be a hint of improvement?   NOTE: DATA statement used (Total process time): real time 17:02.43 user cpu time 2:16.40 system cpu time 19.93 seconds memory 1200.62k OS Memory 23976.00k Timestamp 2019-11-13 10:21:20 AM Step count 36 swtich count 132 Page faults 3 Page Reclaims 7380 Page Swaps 0 Voluntary Context Switches 2226057 Involuntary Context Switches 2113 Block Input Operations 15944 Block Output Operations 263968 ... View more

Re: How to speed up a datastep procedure?

by Quartz | Level 8 in SAS Programming
‎11-13-2019 10:14 AM
‎11-13-2019 10:14 AM
@Tom wrote: Depends a lot one your computing environment.  Where is the source dataset being read from? Where are you writing the result.   If the source is some external database it might help a lot to change the KEEP statement into a KEEP= dataset option on the input dataset as that will limit the amount of data that needs to move from the external database into SAS. Hi @Tom ,   The source is read from an Oracle database (on the network) and I am writing the result in the SAS WORK library.   For the keep options, since those else/if statements create 3 new fields, should I write the keep options in the input dataset with those fields or this is not necessary?  ... View more

How to speed up a datastep procedure?

by Quartz | Level 8 in SAS Programming
‎11-13-2019 10:02 AM
‎11-13-2019 10:02 AM
Hi there,   I have a database which is assigned by a libname and I want to grab data from this dataset which contains 10,664,192 observations. The following code is used to extract a subset of this dataset and the subset itself contains about 1,531,165 observations. The time (real time) for the code to run is 17 min 37 sec, which I find a bit long. Is there a way to improve the code for it to be faster a little bit or there's no improvement possible?   %let MY = 201910; data subset; set myset(where=(source="XYZ")); if put(effective_date, yymmn.) <= &MY. and put(expired_date,yymmn.) >= &MY.; keep field1 field2 field3 field4 field5 field6 ... field14; if state in ("BC") then tx = 0.02; else if state in ("NT") then tx = 0.03; else if state in ("YK") then tx = 0.02; .... (there's 10 more else if statements) run; I am pretty sure there's something I could do tu make this code faster. I tried with options threads cpucount=actual but I think this is not working with datastep procedure. I am using SAS EG on a Grid environment (Unix).   Thanks in advance for any help. This is appreciated. ... View more

Re: Summary report of duplicate values from multiple tables

by Quartz | Level 8 in SAS Programming
‎10-29-2019 09:31 PM
‎10-29-2019 09:31 PM
@PGStats Thank you also for your time and help. I will try your piece of code also tomorrow. 🙂 ... View more

Re: Summary report of duplicate values from multiple tables

by Quartz | Level 8 in SAS Programming
‎10-29-2019 09:30 PM
‎10-29-2019 09:30 PM
@mkeintz Thanks also for your help. Will try it tomorrow. ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎01-30-2020 01:51 PM
Likes given to
Likes from
Quick links