Hi, Here are two other suggestions based on Tom code data abc (drop=_patient_stat); set abc2 (Keep= firstname lastname patient_stat rename=(patient_stat=_patient_stat)); length Product $10 PATIENT_STAT $25 sex2 $4; product = 'ddd'; sex2 = ' '; if _patient_stat = 'L' then PATIENT_STAT = 'LBC'; else if _patient_stat = 'U' then PATIENT_STAT = 'UBC'; run; Suggestion 2 proc format; value $ pstat 'L'='LBC' 'U'='UBC'; run; data abc (drop=_patient_stat); set abc2 (keep= firstname lastname patient_stat rename=(patient_stat=_patient_stat)); length Product $10 PATIENT_STAT $25 sex2 $4; product = 'ddd'; sex2 = ' '; PATIENT_STAT = put(_patient_stat,$pstat25.); run; ... View more

If you're really dealing with a multibyte character set then you need to tell us clearly. For a multibyte character set one can only use string functions on  I18N Level 2.  Which functions are suitable is fully documented here.    From what you write I believe you want the substring of digits up-to the first character in the string. If so then why not use the scan() function ...or kscanx() for multibyte. data test; infile datalines truncover; input str $20.; first_char_pos=kfindc(str,,'kd'); digits_until_first_char=kscanx(str,1,'','kd'); datalines; 1234568F888 ; proc print data=test; run;   ... View more

To add a new variable and give it a value in SQL, you have to use the thiscount - thatcount as new_amount construct, period. If you want to store a value into a macro variable, use SELECT INTO without creating a table and without creating a new SQL variable (no AS needed) ... View more

If I understand your requirements correctly and assume that your 4 right tables have the same columns then something like this should work: proc sql; create table want1 as select '1' as JoinNum ,* from Table1 as T1 inner join table2 as T2 on T1.key = T2.key union select '2' as JoinNum ,* from Table1 as T1 inner join table3 as T2 on T1.key = T2.key union select '3' as JoinNum ,* from Table1 as T1 inner join table4 as T2 on T1.key = T2.key union select '4' as JoinNum ,* from Table1 as T1 inner join table5 as T2 on T1.key = T2.key order by key, JoinNum ; quit; data want2; set want1; by key JoinNum; if first.key; run; The strategy is to do all 4 joins and to give each a priority. By sorting the 4 joins in priority order, you can ensure only the highest priority join is chosen. ... View more

@A_Kh wrote: As per description by the owner (@GN0001) : 2- Each key maps to a couple of subkeys. 3-How can I write the program in a data step that I only capture a couple of subkeys for each key, I don't need all of them.  Assuming the user interested in mapping only 'subkey1' and 'subkey4' among all 'subkey' s, those values were mapped to a new Key variables. But it could be any 'subkey', not only those 2.  Thanks @A_Kh there's a good chance you are right, but we need to hear that from @GN0001 along with a lot more detail. ... View more

It should make it long enough to hold the value you passed, but trailing spaces normally are ignored when making macro variables. If you need to tell SQL what length to use when defining a variable just add the LENGTH= option after the name. "&measure" as new_variable length=4 So if &MEASURE is more than 4 bytes then the value will be truncated.  If it is shorter then SAS makes a normal character variable of length 4 which means the value is padded with spaces to the full length. ... View more

Why is the subject "Proc Print" when the code shown is "Proc Report"?   Where is LOG. Just because it doesn't show errors the log still is likely to show something about why no output is generated. So include the LOG with ALL the code, notes and messages associated with the procedure call.   Nothing like seeing something like this to clarify why no output was generated: 26 27 proc report data=junk; 28 run; NOTE: No observations in data set WORK.JUNK. NOTE: No observations in input data set. Run a report on an empty set => empty report. So, show the log. ... View more

The WHERE clause is applied as the data is read from the source dataset(s).  So you cannot reference variables in the WHERE statement that do not exist in the source dataset(s) because they don't exist! ... View more

As others already implied, there is no one size fits all when it comes to query optimization. Assuming your data is in SAS datasets, some general guidelines though: - Use equi-joins when possible - Avoid OR - Avoid function call in where/on clause - If join presumably return a relative small result set, using indexes on join variables may help - Allow your process to use as much memory as possible to minimize the use of swapping to spill files. - Analyse your query behaviour by using the PROC SQL options _method and _tree, and using the global option FULLSTIMER. ... View more

The two assignment statements are both asking SAS to parse the value of the variable pass to them into "words" based on considering blanks as the delimiter between the words.   The first one is taking the first word it finds. The second one is taking the second word it finds.   Also the two assignment statements are taking the words from two DIFFERENT variables.   The first one is reading from the variable MEMBERNAME and the second one is reading from the variable MEMBERLAST. ... View more

You could let SAS filter the observations on the way INTO the data step. data want; set have; where member in ("A", "B) and code = "T" and payer in ("V"); thisfield = "CCC" ; run; You could use a subsetting IF statement. (required when the condition is based on something calculated in this step). data want; set have; if member in ("A", "B) and code = "T" and payer in ("V"); thisfield = "CCC" ; run; Or use the opposite condition to conditionally DELETE the observation. data want; set have; if not (member in ("A", "B) and code = "T" and payer in ("V")) then delete; thisfield = "CCC" ; run; You could conditionally write the observation in the MIDDLE of the data step (this is your second example with the missing END statement added). data want; set have; if member in ("A", "B) and code = "T" and payer in ("V") then do; thisfield = "CCC" ; output; end; run; Which could more easily be done like this instead: data want; set have; thisfield = "CCC" ; if member in ("A", "B) and code = "T" and payer in ("V") then output; run; ... View more

Thanks, I had a backup of the program so I used that one and I never realized what is the difference one works and one doesn’t work.  respectfully, blue thunder  ... View more

I am outpouring observations only not a dataset. Thanks, Blu blue ... View more

First of all, you have to fix this. Once you fix that, there may (or may not) be additional errors that need to be fixed.   3093 data class ; 3094 infile csv dsd firstobs=2 truncover ; 3095 input name :$8. age sex $ height weight ; 3096 run; NOTE: The infile CSV is: Filename=...\#LN00106, RECFM=V,LRECL=32767,File Size (bytes)=92, Last Modified=31Mar2023:14:32:03, Create Time=31Mar2023:14:32:03 NOTE: Invalid data for age in line 2 8-8. RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0 2 Alfred,M,14,69,112.5 20 name=Alfred age=. sex=14 height=69 weight=112.5 _ERROR_=1 _N_=1     Your input statement has AGE as the second variable that it is supposed to read, and sex as the third variable that it is supposed to read. But in row 2, the first variable is the name Alfred which is read properly, but then instead of age as the second variable and sex as the third variable, the input line for Alfred has sex as the second variable and age as the third variable. ... View more