Thank you. Your solution worked 🙂 ... View more

Thank you. That really helped! ... View more

proc sql; create table want as select *,count(distinct catx(' ',dr_inits,dr_name)) as n_name from name group by icustomerid ; quit; ... View more

Try using PROC SUMMARY instead of PROC FREQ so you can get count and sum in one step.   What number do you want in the FREQUENCY variable?  The number of observations?  Or the number of observations with a non-missing value of RPC? Let's add an observation with a missing value of RPC so you can see the difference. data have ; length Accounts $5 Collected 8 txchari11 $20 Trace_result $3 RPC 8; input Accounts--RPC; datalines; 45461 521 Experian LAS 11 65454 24 CRABlend NEW 54 24534 68 Equifax NEG 23 xxxx 0 Equifax YYY . ; proc summary data=have; class txchari11; var rpc; output out=want n=Frequency sum=rpc_sum; run; proc print; run; Obs txchari11 _TYPE_ _FREQ_ Frequency rpc_sum 1 0 4 3 88 2 CRABlend 1 1 1 54 3 Equifax 1 2 1 23 4 Experian 1 1 1 11 If you don't want that overall summary line (_TYPE_=0) then add the NWAY option to the PROC SUMMARY statement.   ... View more

@Sandeep77 wrote: Hi, As I said that I want to find out from TA_PCODE variable that how many of them are same. Do you want to know "how many are the same", a count, or the actual values that are the same, a data set with the codes in both? This does the latter: data set1; input code $; datalines; a a a b c d b c c ; data set2; input code $; datalines; a c q q q c c c ; proc sql; create table commoncode as select distinct a.code from set1 as a natural join set2 as b ; quit; To find codes in Set1 but not Set2 proc sql; create table in1_not2 a select distinct code from set1 except select distinct code from set2 ; quit; You don't explicitly show what the expected result should look like. @Sandeep77 wrote: Hi, As I said that I want to find out from TA_PCODE variable that how many of them are same. Sample data: Data elnz_lowell_12sep; input Reference_number Add_line_1 Add_line_2 Add_line_ Add_line_4 TA_PCODE; datalines; 24567456 23 Park Lane High street Bradford BD7 1RE 24468265 81 Moudsley Streed Leeds LS12 56452464 49 laistridge lane Manchester M14 6PA ; run; Data Trace_result; input Reference_number Add_line_1 Add_line_2 Add_line_ Add_line_4 TA_PCODE; datalines; 24567428 43 Roysdale road London L24 8PN 54468547 32 Merton Road Leeds LS110 1AB 56452464 49 laistridge lane Manchester M14 6PA ; run;     ... View more

@Sandeep77 wrote: Thank you but it still shows as the first reference_number as 0 and the repeated reference_number as 1. I want if the reference_number is repeated then put it as 1 or else 0. So that I can filter out 0 and conclude them as they are not repeated and unique reference_numbers from the data. I submit that your descriptions need to include example DATA and desired result. ... View more

In SAS discussions MERGE is a "side by side" type of data combining where common named variables will behave quite differently than SQL similar operations. Also a data step merge can have very unexpected results when BY variables have repeated values in two or more data sets and will usually show a warning about such in the LOG. Was there such a warning in the LOG for the data step merge. So when discussing combining data in SAS do not use "merge" unless you mean that side-by-side row combination.   The "union all" is a vertical or stack, which would be an Append procedure operation or SET in the data step.     ... View more

Thank you. I tried this method for the first time and it is definitely a better way to do. Thanks again. ... View more

The error you are showing seems to be more about invalid data for DOB.  Which looks be more about unbalanced quotes (or unquoted commas).   But if the file is really truncating early you might try the  IGNOREDOSEOF option of the INFILE statement. But it is not clear to me if PROC IMPORT would know how to add that to the data step it generates.   You might be better off writing your own data step any way. ... View more

Please note that this is not an ERROR. Errors will stop the data step generated by proc import from running. It is a note about data invalid for a specific variable.   A quick "how to read" one of the messages: NOTE: Invalid data for TA_TEL_NUM_1 in line 40 280-290. This tells you the source line in the file read, Line 40. So if needed you can go to the CSV and examine it. Caution: CSV opened by spreadsheet software may alter appearance of some values and if saved from there alter the data. Also if the Guessingrows option had been set to a minimum of 40, for this file, the ******* would have been considered for setting the variable type.   RULE: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0 40 39,10052229,Yes,,TEL9,Asct Trace No data,Verbal New – 2 Ver,BETHSHEAN CRAIGO,,MONTROSE,ANGUS,DD109LD 101 ,1700,Movement,,,0,0,NO,No,Strong,0,0,0,0,No,0,99,No delete records,No,,No searches ever,No searches 201 ever,Associate,1,3,Some,No,0,0,0,0,No,0,No load records,No delete records,Yes,***********,No search 301 es ever,No searches ever,2,0,Last link in a chain,06042022,68070017907,,,,,,274,Associate,"MR,MARK J 401 AMES, ,HOLLOWAY",09071968,", ,",", ,",", ,",NO,NO,+0176,0,0.5 461 The RULE line is a ruler so you can find the specific columns involved. the + indicate columns 5, 15, 25 etc. The 1, 2, 3 are columns 10, 20, 30 . the 0 is column 100. The line wraps if long enough and the 101, 201 etc. Indicate the column start position of that portion of the line read.   So when you have the invalid data at column 280, you start in the line that that has 201 on the left margin and go across to the column under the 8 in the ruler to find column 280. So you can see the ******** encountered. Compare the informat assigned for that variable: 94 informat TA_TEL_NUM_1 best32. ; The informat is best32. or a generic numeric informat that will accept all sorts of simple numeric values.   This sort of thing, "invalid data" is common from depending on how well your source data is documented. Someone may have a document that says something like "missing values for this field will appear as ********** " (or some other character) Telephone numbers may often be 9999999999 or 0000000000 but I have seen 9990000000 0009999999 as well. Other data sources are likely to have "numeric" fields that are mostly okay but then throw in something like <0.1 or NA or UNK. So sometimes you are best off to create custom informats that will handle those special cases to provide a desired numeric result and prevent invalid data messages.   I have a few data sources where I have custom informats to read site id values because they keep adding such and the custom informat throws this invalid data message so i can question the source about the other metadata that I need to process the data. ... View more

data a; set sashelp.class; run; data b; set sashelp.class; if _n_=1 then delete; run; proc sql; create table duplicate_name as select name from a intersect select name from b ; quit; ... View more

Copy the first 5 lines from the file. Open your file with a TEXT EDITOR to copy the text. On the forum open a text box with the </> and paste the result.   IF the content is sensitive AFTER pasting into the text box type over sensitive values with XXX (character) or 000 (numeric).   One suspects the file is not actually a simple CSV. ... View more