SAS SQL is not really suited for this kind of question (other dialects have RANK summary functions, which can be used), so to get the second highest PRICE for each ID you will have to do something like proc sql; select id,max(price) from( select * from s except select id,max(price) from s group by id) group by id; in other words, subtract the rows with the highest values from the table in order to find the second highest. If you want to use SQL to find the third or fourth highest value, things begin to get complicated, with a larger number of nested queries.   Not that this solution will handle ties differently than the data step I wrote, as that will give different ranks to tied values - a data step that gives the same rank to ties can be written like this: proc sort data=s; by id descending price; run; data ranks; rank=0; do _N_=1 by 1 until(last.id); set s; by id; if _N_=1 or price ne lag(price) then rank+1; output; end; run; But probably the most natural solution in SAS is to use PROC RANK, as suggested by @Patrick  ... View more

I do not quite understand what you are trying to do with the SQL code.   A data step solution can be done like this: proc sort data=s; by id descending price; run; data ranks; do rank=1 by 1 until(last.id); set s; by id; output; end; run; An then it just to use "where rank=2" if that is the rank you want. ... View more

Another possibility is to read the dataset twice in the same data step, e.g.: data want; set have; by can_id; if first.can_id then do until(last.can_id2); set have(rename=(can_id=can_id2) drop=field_1-field_3); by can_id2; output; end; drop can_id2; run; ... View more

I think I found out why - another error in the macro: %let fmt=%upcase(%sysfunc(compress(&format,%str(.),d))); This removes all digits from the format name, including the "8601" in "E8601DA.", so the format name becomes "EDA". The code does not flag this error though, as the  code %let errormsg1=&format is not a valid format.; does not  write the error message anywhere (and the variable is %LOCAL, unless it has been defined outside the macro).   For those who want to see what the macro does, here is a version in readable format: %macro dt_date(date=,interval=,format=,offset=-1,alignment=B,quote=Y)/minoperator; %put Start macro dt_date(date=&date,interval=&interval,format=&format,offset=&offset,alignment=&alignment,quote=&quote); %local interval_temp; %local d fmt rc dsid; %if %superq(date)=%str() %then %let date=&dt_sas; %let interval=%upcase(&interval); %let quote=%upcase(&quote); %let alignment=%upcase(&alignment); %if &format ne %str() %then %do; %let fmt=%upcase(%sysfunc(compress(&format,%str(.),d))); %let dsid=%sysfunc(open(sashelp.vformat(where=(fmtname="&fmt")))); %let rc=%sysfunc(fetch(&dsid)); %let dsid=%sysfunc(close(&dsid)); %if &rc=-1 %then %do; %let errormsg1=&format is not a valid format.; %let jumptoexit=1; %let d=; %goto EXIT; %end; %end; %else %do; %let format=best.; %let quote=N; %end; %let interval_temp=%scan(&interval,1,%str(.)); %let pos=%sysfunc(anydigit(&interval_temp)); %if &pos %then %let interval_temp=%substr(&interval_temp,1,%eval(&pos-1)); %if %eval(&interval_temp in YEAR QTR MONTH WEEK DAY YEARLY QUARTERLY MONTHLY WEEKLY DAILY)=0 %then %do; %let errormsg1=&interval is not a valid date interval.; %let jumptoexit=1; %let d=; %goto EXIT; %end; %if %sysfunc(inputn(&offset, best.))=%str() %then %do; %let errormsg1=&offset is not a valid offset.; %let jumptoexit=1; %let d=; %goto EXIT; %end; %if &quote ne Y and &quote ne N %then %do; %let errormsg1=&quote is not a valid Quote value. Must be Y or N.; %let jumptoexit=1; %let d=; %goto EXIT; %end; %if &alignment ne B and &alignment ne E and &alignment ne M and &alignment ne S %then %do; %let errormsg1=&alignment is not a valid alignment value. Must be B, E, M, S.; %let jumptoexit=1; %let d=; %goto EXIT; %end; %let date=%sysfunc(intnx(&interval,&date,&offset,&alignment)); %let d=%sysfunc(putn(&date,&format)); %if %superq(d)=%str() %then %do; %let errormsg1=&format is not a valid format.; %let jumptoexit=1; %let d=; %goto EXIT; %end; %if &quote=Y %then %let d=%unquote(%str(%')&d%str(%')); %EXIT: %unquote(&d) %put End macro dt_date - Date Value returned is %unquote(&d); %mend dt_date; ... View more

I put the POINT= in the second (PRX) example out of "habit" (copying some code from the first example), it is not necessary here, so you could probably get away with something like data public.want; set public.text_dataset; if _N_=1 then do _N_=1 to words; array prxids (1:1000) 8 _temporary_; set public.dictionary nobs=words; prxids(_N_)=prxparse(cats('/\b',word,'s?\b/i')); end; dict_word_count=0; do _N_=1 to words; dict_word_count+prxmatch(prxids(_N_),text)>0; end; drop word; run; ... View more

does it make sense to use encoding twice here? indeed it does. The first encoding is the encoding used to read the data, the second is the one used for the export file. And they do not have to be the same. Hope that answers your question. ... View more

How do I see this macro program?   Good question. Basically, look in the code above (and code that may have been %INCLUDEd along the way). If the macro is not defined there, you will have to look at the autocall macros. Start by submitting proc options option=sasautos;run; and then look in the places listed - some of them may be physical file names (like "c:\somebody\wrote\these\macros"), others can be logical filenames (like SASMACRO or MACROS - they do not have to be capitalized, but they are not in quotes). Then look for the file in these places, beginning with the ones first mentioned - if there are two versions of your macro in different places, then it is the one in the directory mentioned first that will be used.    If you just want to get an idea of what is going on, try setting the options MPRINT, SYMBOLGEN or MLOGIC, these will show a lot about what happened in the log after submitting your macro call. ... View more

You have a lot better chance at getting an answer if you show your sample data as datastep code, like this: DATA dictionary; input word $; cards; apple banana orange grape ;run; data TEXT_DATASET; infile cards truncover; input text $char200.; cards; This is an apple and a banana I love eating oranges. Grapes are delicious, especially red grapes. I have an apple and an orange for lunch. ;run; The simplest way to get the answer you want is this: data want; set text_dataset; dict_word_count=0; do _N_=1 to words; set dictionary point=_N_ nobs=words; dict_word_count+find(text,trim(word),'i',1)>0; end; drop word; run; - simply reading the "dictionary" in every iteration of the text dataset, and look for the text string.   There is a small problem with your example, however: you apparently want a positive outcome for both "grape" and "grapes" when the search word is "grape" - which is why I used the FIND function and not the FINDW function (which searches for words and not just text strings) - but I suppose that you do not want a positive outcome for words like "grapevine",  "grapefruit", "grapejuice" or "gangrape" (interestingly, but totally irrelevantly, my spelling control accepts "grapevine",  "grapefruit" and "gangrape", but not "grapejuice" - who wrote that?)   If all your words are nouns, that may or may not have an "s" at the end, one possible solution is to use PRX - a PRX expression like '/\bgrapes?\b/i' will look for the whole words "grape" and "grapes" but no other words containing "grape".   A solution for this can be written as data want; set text_dataset; if _N_=1 then do _N_=1 to words; array prxids (1:1000) 8 _temporary_; set dictionary point=_N_ nobs=words; prxids(_N_)=prxparse(cats('/\b',word,'s?\b/i')); end; dict_word_count=0; do _N_=1 to words; dict_word_count+prxmatch(prxids(_N_),text)>0; end; drop word; run; Another possibility is to redo your dictionary, with all relevant forms of the words you are looking for (if only nouns, then it is "apple" and "apples", "die" and "dice", "child" and "children" etc.) and then use the first solution suggested, but with the FINDW function instead of FIND.   ... View more

I think the following code is pretty dangerous: /* Get the macro straight from GitHub */ filename code url "https://raw.githubusercontent.com/SASJedi/sas-macros/master/findfiles.sas"; %include code; and should never be accepted in a serious working environment. I assume (but that is only an assumption) that you are a nice guy that would never put malicious code into your programs on github, but if you yourself are equally careless about running code that you have not even looked at, there is a serious risk that somebody else will hack your computer and plant malicious code in your programs.   Imported code should be revisited before running, and should be copied to a local file to secure against unauthorized change. ... View more

It can be done in two steps, like this: filename tempsas temp; data _null_; set sashelp.cars end=done; array _chars_ _character_; array _lengths_ (1:1000) _temporary_; do _N_=1 to dim(_chars_); _lengths_(_N_)=max(_lengths_(_N_),length(_chars_(_N_))); end; if done; file tempsas; do _N_=1 to dim(_chars_); _name_=vname(_chars_(_N_)); put 'length ' _name_ '$' _lengths_(_N_) ';'; end; run; data cars; %include tempsas/source2; set sashelp.cars; run; So, basically, first you generate the LENGTH statements in a temporary file, then you execute them with %INCLUDE. The example shown will generate a warning in the log: WARNING: Multiple lengths were specified for the variable Type by input data set(s). This can cause truncation of data. - which actually shows that the TYPE variable is longer than necessary in the original data set.   I am not sure that there is that much to be gained by shortening the lengths of character variables, though. If you want to save disk space, it is much more efficient to use the  options compress=char; statement before creating the output data. ... View more

It can be done with a single data step, like this: data want; merge mydata(drop=claim) mydata(keep=patient_id claim where=(claim=1)); by patient_id; run; ... View more

Here is a possible solution - it is not so easy, as you basically can have two userid's with both zip and email address different actually being the same user (as userid 1 and 3).   First, find the two sets of links from one userid to the next: proc sort data=have; by firstName lastName zip descending enrollmentDate; run; data link1; do until(last.zip); set have; by firstName lastName zip; if first.zip then link1=userid; output; end; run; proc sort data=have; by firstName lastName emailAddress descending enrollmentDate; run; data link2; do until(last.emailAddress); set have; by firstName lastName emailAddress; if first.emailAddress then link2=userid; output; end; run; Next, integrate the two link tables: proc sort data=link1; by userid; run; proc sort data=link2; by userid; run; data link; merge link1 link2; by userid; if link1 ne userid then link=link1; else if link2 ne userid then link=link2; drop link1 link2; run; Finally, index the LINK table and use it to find the final link for each userid: proc sql; create index userid on link(userid); quit; data want; set link; do while(link ne .); newUserid=link; set link(keep=link userid rename=(link=newlink userid=link)) key=link/unique; link=newlink; end; if newUserid=. then newUserid=userid; drop link newlink; run; ... View more

I think you can get what you want by counting the number of words in the string, e.g.: data firstlast; input string $60.; First_Word=scan(string,1, ' '); if countw(string,' ')>1 then Last_Word=scan(string, -1,' '); datalines; Jack and Jill & Bob & Carol & Ted & Alice & Leonardo Gates ! $ % & ( ) * + , - . / ;;;; run; ... View more

How we can make the footnote1 to appear and disappear based on pdf page numbers? I don't think you can. What you can do is this: ods escapechar='^'; Footnote '^{pageof}'; which should give you a footnote like 'Page 3 of 7'.   This should work in destinations like RTF and PDF, but not in the normal listing destination. ... View more

Yes, @Kurt_Bremser is right. When it comes to .csv-files, it is much better to use data-step code than PROC IMPORT. You do not have to write the whole data step yourself, though. After submitting PROC IMPORT, you can use the RECALL command once in a text editor window, or scrape the generated data step code from the log window. You will then have not the PROC IMPORT code, but the data step code that PROC IMPORT generated. Adapt that changing the names of variables (and possibly other stuff), and save the code. You will then have something that gives similar data type results every time, even if the input data varies (PROC IMPORT guesses the types of the variables by looking at the file). ... View more