Your SQL code does not work with your sample data.  If you could supply SQL code that works properly with the data (and data which has numeric variables where the SQL expects them), then it will be much easier to determine the equivalent DATA step code.   The problem you have identified, doing a many-to-many in a DATA step takes a bit of coding.  But use of a hash object can make it reliable and useful. ... View more

@hhchenfx wrote: Hi, I meant simple US business day or normal week day. Thanks, HHC   I'll reiterate: there ain't such a thing.  If you mean to exclude Federal Holidays on the observed days that's fine, say so but many organization pick and choose which to follow. The SAS Holidayname function returns the names of holidays associated with a given date. So you can test a candidate date to see if it is one of the holidays you expect to observe. If it is then use the INTNX function to get the previous day to test. When I worked for a University they did not observe Columbus Day or Veterans day. Many many companies work every day of a typical week and only observe a very few holidays. A survey company I worked for that typically worked Saturdays  would not work on Saturdays that coincided with certain sporting events. ... View more

Select the makes, then extract the sample:   proc sql; create table makes as select unique make from sashelp.cars; quit; proc surveyselect data=makes n=5 out=makes_5 seed=123; run; proc sql; create table cars_samp as select * from sashelp.cars where make in (select make from makes_5); drop table makes_5; quit; ... View more

Thank you this works! ... View more

At first sight, the only thing suspicious to me in your query is   select ...., PID, count(distinct PID) .... from .... group by PID   if you group by PID, then count(distinct PID) will be one, by definition.   Also note that the first subquery will require remerging because you do not group by INSERTDATETIME. This will likely generate more records than you expect. Either remove INSERTDATETIME from the select list or add INSERTDATETIME to the group by clause. ... View more

Here is a simple datastep that does the job. Less sophisticated than @FreelanceReinh 's suggestion but it should provide a good starting point :   data want; set have; array x George -- Many_more_name; do i = 1 to dim(x); if upcase(vname(x{i})) = upcase(select_person) then do; x{i} = 0.8 * x{i}; leave; end; end; drop i; run; ... View more

Thank you very much. That's exactly I want.  Thanks again ... View more

It is really not easy. Here is the code you could start with .   data test; length txt $60; input txt &; datalines; 0-2004-editcacacon.sas 0-2005-editcacacon.sas 0-2112-editcacacon.sas 1.1 sald-etudettsald_2018.sas 1.1 sald-etudettsald_2020.sas 1.1 sald-etudettsald_2020-base 2018.sas 1.1 vie-etudettvie_2018.sas 1.1 vie-etudettvie_2020.sas 1.1 vie-etudettvie_2020-qx_anc.sas 1.2 sald-kkldmajtab_2018.sas 1.2 sald-kkldmajtab_2020.sas 1.2 vie- kkviemajtab_2018.sas 1.2 vie-kkviemajtab_2020.sas 1.2 vie-kkviemajtab_2020-qx_anc.sas 9.9 2002 pouet-pouet.sas ; proc sql; create table have as select a.txt as a_txt,b.txt as b_txt ,spedis(compress(a.txt,,'pds'),compress(b.txt,,'pds')) as distance from test as a,test as b where calculated distance<50 order by a.txt,distance ; quit; ... View more

Thanks for your help. The code I shared on the forum runs without producing an error message. I will add what you've provided to my code when I return to work next week. I'll let you know if it works. Thanks again.  ... View more

In my experience, if you are doing simple, sequential DATA step reads, experimenting with DO loops is a waste of time - stick with just a SET statement as adding DO's will have minimal impact on performance. If you want to optimize your code focus on other areas, especially any really slow processes. ... View more

Thanks ... View more

To run SQL code in SAS you need to use PROC SQL. You need to make sure your SQL is compatible with SAS.  Every implementation of SQL has it own distinct features and quirks.   The main issue with your posted code is the strange text at the end of one of the lines.  Perhaps the SQL implementation that was used to run that code in the past allowed some type of "end of line" commenting and those double dashes are the trigger for that?  I do not like end of line comments in general, you should place the comments before the code they are describing if you want to make it easier for humans to read the code.   Another issue is why would you run code like that without directing the output to a dataset.  If you want to print the data, like that first query, then just use PROC PRINT and skip SQL completely.   Also to reference a dataset from a library you use only one period between the libref and the member name.   Also if you have month day and year why not just make an actual DATE variable?     proc sql; create table copy_of_raw as select * from try.a_RAW ; create table raw_with_final as select distinct '11111' as ID , a.SUBJID /* study team need DOB */ , mdy( b.DOB_M , b.DOB_D , b.DOB_Y) as DOB format=date9. , a.car as type , a.g6m as month , a.g2yr as 24month , a.g1yr as 12month , a.g10yr as decade from try.a_RAW a join try.FINAL b on b.ID = '11111' and a.SNO = b.SNO ; quit;   ... View more