It seems you can use functions from DS2 packages in FEDSQL. So something like this may be a workaround: /* DS2 code */ proc ds2; package prxfunc; method match(char prx, char source) returns integer; return prxmatch(prx,source); end; endpackage; run; quit; /* fedsql code */ proc fedsql; select * from mytable where prxfunc.match('/Joe/',firstname); quit; ... View more

Something like this should do it: data want; length record_id $40 date time 8 user fname mname lname $20 recordno 8 person_id $40 x 8 jobprofile $40 city code adr1 adr2 adr3 pincode $20; informat date yymmdd. time time.; format date yymmdd10. time time8.; infile cards firstobs=2 eof=done; /* firstobs=2 to drop the first separator line */ input record_id date time user fname mname lname / recordno person_id x jobprofile / @ ; do while(_infile_ ne: '========='); input city code adr1 adr2 adr3 pincode /@; output; end; done: cards; =============================================================================== UNIQUEID_RECORD00001 2018-06-05 01:00:00 USER001 FNAME1 MNAME2 LNAME1 00001 PERSONAL_RECORD_00000000001 00000000000000000001 JOBPRFL1 CITY0001 CODE1 ADDRESS1 ADDRESS2 ADDRESS3 PINCODE1 CITY0002 CODE2 ADDRESS1 ADDRESS2 ADDRESS3 PINCODE1 =============================================================================== UNIQUEID_RECORD00002 2018-06-05 01:00:00 USER002 FNAME1 MNAME2 LNAME1 00002 PERSONAL_RECORD_00000000001 00000000000000000002 JOBPRFL2 CITY0003 CODE3 ADDRESS1 ADDRESS2 ADDRESS3 PINCODE1 CITY0004 CODE4 ADDRESS1 ADDRESS2 ADDRESS3 PINCODE1 CITY0005 CODE5 ADDRESS1 ADDRESS2 ADDRESS3 PINCODE1 CITY0006 CODE6 ADDRESS1 ADDRESS2 ADDRESS3 PINCODE1 =============================================================================== UNIQUEID_RECORD00003 2018-06-05 01:00:00 USER001 FNAME1 MNAME2 LNAME1 00003 PERSONAL_RECORD_00000000003 00000000000000000031 JOBPRFL3 CITY0002 CODE2 ADDRESS1 ADDRESS2 ADDRESS3 PINCODE1 ;run; Of course, you will probably want to use an external file as your infile, and drop the CARDS section.   The EOF=DONE thing is a workaround in case there is no separator line after the last data line. ... View more

That's probably because you have some observations in your real data with one or more variables missing. If you do not want to see those, you can drop them when creating the MOVES table: data moves; set have; array cities(2000:2002) city2000-city2002; do year=2001 to 2002; from=cities(year-1); to=cities(year); if from ne ' ' and to ne ' ' then output; end; keep from to year; run; ... View more

When looking at the ruler for the first record in non-proportional font, I see this: ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0 "1282,6.28125E+12,""PAO, PFAD"",sell,QN17101282,10/24/2017 6:55,10/24/2017 6:55,open,1509429650,ID,F Looks very strange, as the initial double-quote is apparently not read as a quote (is something wrong with the log display?), and trade_customer_id resolves to "1282 while the phone number correctly resolves (incorrectly, but that's because there is an error in the input file) to 6.28125E+12. Next, I think the name should have been "PAO, PFAD" and the position "sell", but because the quotes are not interpreted as such (or because they are doubled?), the name becomes ""PAO and the position becomes PFAD. And then all the following variables are also read "too early" and the program tries to read "open" for the number valid_till_ts, which should have been 1509429650.   There is something wrong with that .csv -file. Maybe that the whole line has been quoted when the file was written.      ... View more

@saseles: It seemed from your initial specification that you did not want the a to a, b to b and c to c connection counted as moves. But if you want those too, just get rid of the code to remove them: data moves; set have; array cities(2000:2002) city2000-city2002; do year=2001 to 2002; from=cities(year-1); to=cities(year); output; end; keep from to year; run; proc freq data=moves noprint; tables year*from*to/sparse out=want(rename=(count=number) drop=percent); run; This seems to be the same as your WANT data, except that yours are not sorted. ... View more

I can see a couple of errors in your code: You forgot a left paranthesis in "output.file.name" ||TRIM(PGM)|| ")"; You put your filenames in INP_NAME and OUT_NAME, but your FILEVAR variables are named IDS_NAME and ODS_NAME Not sure if that is enough to accomplish what you want, but it may help. ... View more

Here is a possible solution, which uses PROC FREQ with the SPARSE option (to get all possible combinations): data moves; set have; array cities(2000:2003) city2000-city2003; do year=2001 to 2003; from=cities(year-1); to=cities(year); if from ne to then output; end; keep from to year; run; proc freq data=moves noprint; tables year*from*to/sparse out=want(rename=(count=number) drop=percent); run; A lot less complicated than the hash stuff. ... View more

Using arrays is generally the way to do these things. For the first step, I would use the COALESCE function: data the_matrix_query;   set the_matrix_query_withnull;   array years(*) 8 year:;   do _N_=1 to dim(years);     years(_N_)=coalesce(years(_N_),0);     end; run; Of course, you may want to make the array data explicit (year2009-year2017) instead of year:, if you have other variable names beginning with "year". The second step can be done in a similar way, except that you cannot use coalesce, but must use if...then.   The last two steps can be put into a single step: data makevector;   set the_matrix_query;   where week<53;   array years(2009:2017) 8 year2009-year2017;   do year=2009 to 2017;     count=years(year);     output;     end;   keep week year count; end; I am not sure why you did not put the week<53 condition on 2014. Is that because there was no week 53 in 2014, or because you really want week 53 included for 2014 specifically? In the latter case, you will have to change the code a bit, e.g.: data makevector;   set the_matrix_query;   array years(2009:2017) 8 year2009-year2017;   if week<53 then do year=2009 to 2017;       count=years(year);     output;     end;   else do;     count=year2014;     year=2014;     output;     end;   keep week year count; end; ... View more

To calculate the number of times each light has been switched on, you can do something like this: data want;   n_switch_on=0;   do until(last.light_no);     set have;     by Light_no on_off_status notsorted;     n_switch_on+first.on_off_status and on_off_status='ON';   end;   keep light_no n_switch_on; run;   Unless, of course, you do not want to count if the light is on in the first hour, then the last line in the loop should be     n_switch_on+first.on_off_status and not first.light_no and on_off_status='ON'; ... View more

While a format can work for random access, as suggested by @Kurt_Bremser, using SET with KEY= is probably faster if you have a lot of data: proc sql;   create index idx on three(cardtl,acctdtl); run; data want;   set one;   if missing(CD_B) then do;     if missing(CD_A) then       set three key=idx/unique;     else       set three(drop=CD_A) key=idx/unique;     end;   else if missing(CD_A) then     set three(drop=CD_B) key=idx/unique; run; I made the code a bit more complicated than absolutely necessary (by checking if both variables were missing, and then just using one SET statement), as that will probably give better performance with large tables. ... View more