Folks,   I'm carrying out some analysis to produce counts by different categories. I'm using the following code;   /*Board Ages*/ proc summary data = full_dataset_3 nway missing; ; format year_filed year_filed. county_y $county_y. age_filed_total age_filed_total. ; class year_filed county_y age_filed_total /mlf; var Number_Person; output out = Number_of_people (drop=_freq_) sum= ; run; However, I need to do some disclosure control so nothing can be identified. Any value which is less than 20 will get set to zero and I want to round all values to the nearest tenth e.g. 255 gets rounded to 260 and 123 gets rounded to 120.   However, I would also like my subtotals to add up to the total e.g. when I round all the subtotals I need to recalculate the totals again.   Basically I need to go from my original data  to this new one. Would anyone be able to provide some guidance? I would rather round cells rather than suppressing. data example; length year $4. location $11. cohort $17.; input year $ location $ cohort $ org rounded; infile datalines delimiter=','; datalines; 2010,All regions,: All ages,1657,1650 2010,All regions,100 0 - 24 years,27,20 2010,All regions,101 25 - 35 years,266,270 2010,All regions,102 36 - 45 years,299,300 2010,All regions,103 46 - 59 years,246,240 2010,All regions,104 60+ years,121,120 2010,All regions,105 Unknown,698,700 2010,Region A,: All ages,453,440 2010,Region A,100 0 - 24 years,22,20 2010,Region A,101 25 - 35 years,149,150 2010,Region A,102 36 - 45 years,99,100 2010,Region A,103 46 - 59 years,83,80 2010,Region A,104 60+ years,32,30 2010,Region A,105 Unknown,75,80 2010,Region B,: All ages,365,670 2010,Region B,100 0 - 24 years,5,0 2010,Region B,101 25 - 35 years,117,120 2010,Region B,102 36 - 45 years,200,200 2010,Region B,103 46 - 59 years,163,160 2010,Region B,104 60+ years,89,90 2010,Region B,105 Unknown,623,620 ; run; ... View more

Folks,   I have a number of address that I want to search for a country name.   At present I'm using the following code.   data countries; set test; if index(address,"LONDON")>0 THEN countyname_1='UK'; if index(address,"MANCHESTER")>0 THEN countyname_1='UK'; if index(address,"LEEDS")>0 THEN countyname_1='UK'; if index(address,"SCOTLAND")>0 THEN countyname_1='UK'; if index(address,"WALES")>0 THEN countyname_1='UK'; if index(address,"LIVERPOOL")>0 THEN countyname_1='UK'; if index(address,"SCOTLAND")>0 THEN countyname_1='UK'; if index(address,"ENGLAND")>0 THEN countyname_1='UK'; if index(address,"GLASGOW")>0 THEN countyname_1='UK'; if index(address,"Edinburgh")>0 THEN countyname_1='UK'; if index(address,"BRISTOL")>0 THEN countyname_1='UK'; if index(address,"CHESHIRE")>0 THEN countyname_1='UK'; if index(address,"BIRMINGHAM")>0 THEN countyname_1='UK'; if index(address,"ESSEX")>0 THEN countyname_1='UK'; if index(address,"BIRMINGHAM")>0 THEN countyname_1='UK'; if index(address,"SHIRE")>0 THEN countyname_1='UK'; if index(address,"SURREY")>0 THEN countyname_1='UK'; if index(address,"SUNDERLAND")>0 THEN countyname_1='UK'; if index(address,"DEVON")>0 THEN countyname_1='UK'; if index(address,"NEW YORK")>0 THEN countyname_1='USA and Canada'; if index(address,"NY")>0 THEN countyname_1='USA and Canada'; if index(address,"USA")>0 THEN countyname_1='USA and Canada'; if index(address,"CANADA")>0 THEN countyname_1='USA and Canada'; if index(address,"TORONTO")>0 THEN countyname_1='USA and Canada'; if index(address,"BELFAST")>0 THEN countyname_1='Northern Ireland'; if index(address,"DERRY")>0 THEN countyname_1='Northern Ireland'; if index(address,"NORTHERN IRELAND")>0 THEN countyname_1='Northern Ireland'; if index(address,"ANTRIM")>0 THEN countyname_1='Northern Ireland'; if index(address,"ARMAGH")>0 THEN countyname_1='Northern Ireland'; if index(address,"TYRONE")>0 THEN countyname_1='Northern Ireland'; if index(address,"AUSTRALIA")>0 THEN countyname_1='Australia and New Zealand'; if index(address,"ZEALAND")>0 THEN countyname_1='Australia and New Zealand'; if index(address,"QUEENSLAND")>0 THEN countyname_1='Australia and New Zealand'; if index(address,"FRANCE")>0 THEN countyname_1='Mainland Europe'; if index(address,"SPAIN")>0 THEN countyname_1='Mainland Europe'; if index(address,"GERMANY")>0 THEN countyname_1='Mainland Europe'; run; The problem is that certain addresses have maybe 'York' and 'new York'. Is there a way to use the index function to read a string from right to left, as usually the country is written in the last area?   Also, is there a more efficient way to search in strings? Originally I was going to write all my uk regions into a macro and search for them seperarly. I'm not sure the best way to search though. Is it with the index option?   proc sql; select name into :UK_LIST separated by '|' from test; quit; ... View more

Each month I will have to process raw payroll information and create an analysis dataset out of it for people within my compnay.   Here is my current thinking.   When I get the raw March 2020 data, I will break this out into smaller datasets based on the paymonth. So all January payments will be put into a SAS dataset called Jan_2020M03, all  February payments into Feb_2020M03 etc. The M03 allows me to identify which month the raw data has came in on.   However, I will need to recreate my analysis dataset each month. To create the Jan_2020Analysis dataset I will need to.   First read in the Jan_2020M01 dataset. I will then need to read in Jan_2020M02 dataset. From here I will need to overwrite information in the Jan_2020M01 information with information Jan_2020M02. I can do this with a unique payslip id. However, I will need to read in the Jan_2020M03 and potentially overwrite information in the Jan_2020M01 and Jan_2020M02. This will need to keep occurring until I have read in all the Jan_2020 datasets. Once this is done, I can then create my analysis dataset.   I need some dynamic way to this. My folder structure is as follows;   \\location\2020\2020M01   So within the above location I have 12 sas datasets Jan_2020M01...Feb_2020M01....Dec_2020M01   However, I have 12 other folders such as   \\location\2020\2020M02   So within the above location I have 12 sas datasets Jan_2020M02...Feb_2020M02....Dec_2020M02   So I need a way for SAS to only pick out the datasets I want. So when I want to recreate the January file pick out all the January datasets.   I would appreciate any advise. ... View more

Would appreciate any help on this matter.   I have a large json file 12 million records, which I've split up into smaller json files for processing efficiency.    I have some code which can process these files, however I would like to include it in a macro so it'll carry out the work for each seperate json file.   %let target=\\location\data\data_set_; %let n=100000; data _null_; if eof then stop; length filename $200; filename=cats("&target",_n_,'.json'); file out filevar=filename ; sep = '[' ; do i=1 to &n until (eof); infile json2 end=eof; input ; put sep $1. _infile_; sep=','; end; put ']'; run; *Now that we have our correctly formated raw JSON file we can use the SAS json engine mapper to read in our JSON file and output into SAS datasets. The location real_map is the custom map we have created which assigns labels also. It outputs the data into two datasets of interest 1 - Evertything includes most of the information needed 2 - address_addressLines contains the address information 3 - A third dataset is created called all_data. This information is not needed but cannot be suppressed from being output so this can be deleted afterwards; filename json3 "\\location\data\data_set_.json"; * This location has all the smaller json files libname json3 JSON fileref=json3; filename real_map "location\full_json_map_with_labels.map";*/ libname json3 json fileref=json3 map=real_map; proc copy in=json3 out=out_json memtype=data; run; *For efficiency we will put all 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(',',add_1,add_2,add_3); run; *Join the address data with the other data which we will called data_of_interest e.g. 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; *Ordinal root is the linking key; proc sql; create table out_json.data_of_interest as select * from out_json.everything full join work.address on everything.ordinal_root=address.ordinal_root; quit; So all this works fine, but basically I need to be able include it in a macro which will output these json files into a number of separate sas files.   Would anyone be able to provide any input? ... View more

Folks,   I have two variables on income, and I would like to choose whatever the biggest value is to put in a third one. However, in certain cases they will be the same so I need to let SAS know that in this case it can choose either.   Example   data have ; input id $ Var1 Var2 Var3 ; cards; a1 15000 20000 20000 a2 30000 5000 30000 a3 10000 10000 1000 run; Any help would be welcome  ... View more

Folks,   I have data which I'll need to update as I go along. It's payslip information where old data will have to be overwritten with amended records.   My unique identifier per row is called payslip_id and my unique identifier to identify which payslips records need to be amended is called  previous_payslip_id. previous_payslip_id will correspond to the value in payslip_id which needs to be overwritten. See below for an example;     So this person has for four records. So the information on record 3 needs to overwrite the records in record 2 and then subsequently the information in record 4 needs to overwrite the information from record 3 which is now in record 2. So after this person will only be left with two entries. The first one and the last.    Can anyone think of an efficient way to do this, as the example above can occur multiple times for an individual but the logic behind it is the same.  ... View more

Folks,   I'm working trying to make some code as a efficient as possible. Basically I will have master dataset which I'll need to put into smaller datasets (based on months) as I receive data. So when I receive data of circa 4million records I'll need to move those records into different datasets depending what date they have on them.   I've created some code but I'm running into problems. See below for what I've written.   *Amend the raw file you receive ; data delivery_n ; set data_of_interest; time=catx('-',paydate,time_stamp); date = input(paydate,yymmdd10.); format date date10.; newdate = put(date,yymmn6.); period='Period'; newdate1=catx('_',period,newdate); run; *Write the distinct months based on variable created newdate1 proc sql noprint; select distinct compbl(newdate1) into :period separated by " " from month_t; quit; filename x temp; data _null_; attrib period length=$6 ds length=$18; file x; i=1; do while(scan("&period",i," ") ne ""); ds=scan("&period",i," "); period=scan(ds,-1,"_"); put "if period=""" period +(-1) """ then output " ds ";"; i+1; end; run; *Output your data into separate monthly datasets %macro i(range_high,range_low); %local offset start_date end_date suffix; %do offset=0 %to %sysfunc(intck(month,&range_high,&range_low)) %by -1; %let start_date=%sysfunc(intnx(month,&range_high,&offset,b)); %let end_date=%sysfunc(intnx(month,&start_date,0,e)); data &period ; set month_t; %inc x if &start_date <= date <=&end_date then output i; run; %end; %mend i; %i('01dec2019'd,'01jan2019'd); I'm getting the following issue;   WARNING: Source line spooling has been disabled. %INCLUDE of line numbers is not available. NOTE: The SAS System stopped processing this step because of errors. NOTE: There were 402033 observations read from the data set WORK.MONTH_T. WARNING: The data set WORK.PERIOD_201901 may be incomplete. When this step was stopped there were 402033 observations and 83 variables. WARNING: The data set WORK.PERIOD_201902 may be incomplete. When this step was stopped there were 402033 observations and 83 variables. WARNING: The data set WORK.PERIOD_201903 may be incomplete. When this step was stopped there were 402033 observations and 83 variables. WARNING: The data set WORK.PERIOD_201904 may be incomplete. When this step was stopped there were 402033 observations and 83 variables. WARNING: The data set WORK.PERIOD_201912 may be incomplete. When this step was stopped there were 402033 observations and 83 variables. The datasets are not separating out but appear to be writing all observations to each separate dataset.   Any help would be great. ... View more

Folks,   I have a dataset of transactions for people and I need to identify which ones are the latest. I have a timestamp variable which we can call time_stamp and I have an ID variable which we call ID.   Timestamp is numeric with larger values meaning its more recent to the system.    So I did to run some code that outputs the latest value for an individual when the timestamp is the largest for that id.   Could anyone supply and ideas please? ... View more