I have the following datasets in a list; proc sql noprint; select distinct MEMNAME into :MEM_LIST separated by " " from DICTIONARY.COLUMNS where LIBNAME="PMODA"; quit; %put &MEM_LIST; APR_19 APR_20 AUG_19 DEC_19 FEB_19 FEB_20 JAN_19 JAN_20 JUL_19 JUN_19 MAR_19 MAR_20 MAY_19 NOV_19 OCT_19 SEP_19 YEAR_TO_DATE_19 However, I want to only have datasets which end in _19 in my variable called mem_list.   I've tried to use and memname like "19%" in my sql query but this does appear to pick it up.   Can anyone provide some suggestions, please? ... View more

Folks,   I have a macro that runs through N json files and parses them into SAS datasets. However, I've noticed in certain cases the dataset which has address information will be blank as it addresses are not mandatory. As a workaround to this I attempted to put in a if then else do, but SAS appears to be ignoring it. Does anyone have any idea why this is?   %macro json_map(DirectoryAndDataset= ); /*Firstly we read in the dataset called DatasetsInADirectory and keep one record each time depending on what iteration we are in*/ data DatasetsInADirectory_1; set DatasetsInADirectory; where DirectoryAndDataset="&DirectoryAndDataset"; run; /*Use the filename function to read in the individual json file which we have broken into N batches*/ filename json3 "\\collect01\\Migration_03\Datasets\Rev\PM\Rec_&date\paye\&DirectoryAndDataset..json"; /*The next three steps initialises the SAS JSON engine. real_map refers to the custom json map we create which puts the parsed data into an easier to handle SAS dataset. When the json engine stops parsing the data it outputs it to the libname called json3*/ libname json3 JSON fileref=json3; filename real_map "\\collect01\\Migration_03\Datasets\Rev\PM\full_json_map_with_labels.map"; libname json3 json fileref=json3 map=real_map; /* SAS is unable to directly write the parsed json files to another location. Therefore use proc copy to write the datasets you want into the location out_json. Exclude the dataset called alldata as it is not needed We are left with two datasets after this*/ proc copy in=json3 out=out_json memtype=data; exclude alldata; run; /*Appears to be an issue copying a dataset straight over using a SAS mapping engine using a datasetp. This causes issues later on. Use an SQL step to create the temp dataset*/ PROC SQL; CREATE TABLE everything_1 AS SELECT * FROM out_json.everything; QUIT; /*It appears that supplying address information is not mandatory so in some of the n batches the out_json.address_addresslines will be blank. Putting in a caluse is casues issues too so just create a dummy dataset if N=0*/; proc sql noprint; select count(*) into :rowCount trimmed from json3.address_addresslines ; quit; %put NOTE: &=rowCount; %if &rowCount = 0 %then %do; data out_json.&DirectoryAndDataset; length json_file $40; set everything_1; json_file="&DirectoryAndDataset"; run; %end; %else %do; /*For the dataset out_json.address_addresslines we transpose the address information into one variable*/ proc transpose data=out_json.address_addresslines out=addresses prefix=add_; by ordinal_root; var addressLine; run; data address; set addresses; full_address=catx(',', of add_:); run; /*Use SQL to join the two datasets called out_json.everything and work.address to create the dataset out_json.data_of_interest This should create a replica of what appears in the json file i.e. each payslip record in the json file will have the same record in the SAS file; We use Ordinal root as the linking key;*/ proc sql; create table out_json.data_of_interest as select * from out_json.everything full join work.address(rename=(ordinal_root=ordinal_temp)) on everything.ordinal_root=address.ordinal_temp; quit; /*Finally we use the proc datasets option to delete the temporary datasets address_addresslines and everything. We rename the dataset out_json.data_of_interest to &DirectoryAndDataset (this depends on what Json you're reading into) so when the macro runs through each dataset it doesn't write over the previous dataset*/ proc datasets lib=out_json nodetails nolist; delete address_addresslines everything; change data_of_interest=&DirectoryAndDataset; run; data out_json.&DirectoryAndDataset; length json_file $40; set out_json.&DirectoryAndDataset; json_file="&DirectoryAndDataset"; /*Also create a variable called json_file so you know from which record each json file comes from. This allows us to go back into the raw json file to check if there is an issue with the actual data or just how it was proccessed*/ run; %end; %mend json_map; *The above is the macro which is used, the below is the function which carries the process out for all N datasets; /*%json_map(DirectoryAndDataset= ); /*Put a timer at begining and end to see how long processing takes for everything*/ %let datetime_start = %sysfunc(TIME()); %put START TIME: %sysfunc(datetime(),datetime14.); data macro_call; set DatasetsInADirectory; *build macro call string; str = catt('%json_map(DirectoryAndDataset =', DirectoryAndDataset, ');'); *call macro; call execute(str); run; %put END TIME: %sysfunc(datetime(),datetime14.); %put PROCESSING TIME: %sysfunc(putn(%sysevalf(%sysfunc(TIME())-&datetime_start.),mmss.)) (mm:ss); I know dataset json file paye_123 has no address information so that dataset  out_json.address_addresslines will be blank. However, SAS will not take this into account when I run macro_call function. It will however, when I individually run one dataset at a time into the macro. Is there a reason for this? ... View more