Hi Tom, I'm just wondering is it possible to add labels at this stage too? So I would like to have labels for my variables in my stamp too? ... View more

SAS is reading it in in scientific notation    So the following values   1545317114383 Comes out as such   1.5453171E12 ... View more

Hi Tom,   How would I see the map which SAS generates? ... View more

Hi Tom,   Thanks very much for this. Your code successfully loaded it into json. However, I'm running into some issues which perhaps I could ask you about.   When I run the libname json command    libname json3 JSON map=map2 automap=reuse ; The json file is parsed into a number of separate datasets.   I'm aware that SAS creates an ordinal variable which should allow you to link the tables back together again. However, given the structure of my json file I'm running into some issues.   Take "address" for example. The json engine is separating this into one dataset. However, a number of records contain no information of this kind.  So if the first reference of address doesn't appear until record 26 SAS is assigning an ordinal root key of 1 as it's the first time it encounters it. Therefore, if I start linking back up, I'll be linking to the wrong record. This type of issues will be the same for other cases where a variable isn't on a record. So I need ensure an accurate unique ID is on each table.   I have two possible workarounds, but need some guidance around them.   Your code supplied below allowed me to alter the json file so I could read it into SAS.   However, is it possible to extend this to create a enumeration variable called record_id which would be =1 for the first record and =n for the nth record?   data _null_; infile json2 end=eof; file json3 ; input ; if _n_=1 then put '[' @; else put ',' @; put _infile_; if eof then put ']'; run; Secondly, when I'm using the libname json command is it possible to tell SAS to ensure that the variable record_id appears on every table which is parsed?   So say for table which relates to addresses, when this is created the ordinal root key would be 1,2,3 as it only encounters this variable 3 types but the record_id would be 26,500,23,000 as these are the records it relates to?   I would appreciate any help on this.   ... View more

Hi Chris,   I just want to make sure when I read the json file into Sas that they're not saved to some common area where everyone can view them.   ... View more

filename in "\\jsonlocation\\Simdata.json"; filename map 'my.map'; libname in_json json map=map automap=reuse; Running this piece of code produces an error of the following nature.   Relating to an invalid filename.   However, this works   filename in_json "\\jsonlocation\\Simdata.json"; filename map 'my.map'; libname in_json json map=map automap=reuse; I presume your file name location name must be the same for your libname location for reading in the json ... View more

Hi Tom,   This appears to be doing the trick.   I just have one question.   As this macro will be creating a dataset for each given month is it possible to include a piece of code that would create a distinct dataset rather than writing over each additional one?   So like   i= 1......n;   data output_&i; set month; run;     ... View more

Hi Kurt,   Thank you for your help so far.   To be honest I'm still a bit lost about all of this. I've put up some of my code to give a better idea what I'm currently doing and what I'm trying to do.   /*Manually adding your Rules based categories*/ %let start_date='01nov2017'd; *First day of analysis month; %let end_date='31dec2018'd; * Last day of analysis month; %let end_date1='30nov2017'd; *Last day of lookback month month previous to above; /*Copy of your dataset*/ data testa; set a.analysis_dataset; run; /*Subset for your lookup month*/ data subset; set testa; if &start_date <= dt_filed <=&end_date then result="match"; if result='match' then output subset; run; data month; set subset ; if substr(id,1,1) in ('X') then delete; if dc_type_participant='Purchaser' then output month; run; data month_t; length type_of_buyer classification $50.; set month; if dc_res_purch_info='First Time Buyer Owner-Occupier' then type_of_buyer ='First Time Buyer'; else if dc_res_purch_info ne 'First Time Buyer Owner-Occupier' then type_of_buyer='Previous Residential Home Owner'; run; proc sort data=month_t; by id; *Create your previous months data which omits the latest month again only looking at those who purchased a house previously and a res one; data previous_t; set a.full_period; where dt_filed between '01JAN2010'd and &end_date1; if substr(id,1,1) in ('X') then delete; if dc_type_participant='Purchaser' then output previous_t; run; proc sort data=previous_t(keep=id); by id; *Compute the number of times someone has appeared previously; proc summary data=previous_t sum; by id; output out=appearances_buyer; run; data appearances_buyer(drop=_type_); set appearances_buyer; rename _freq_ = estamp_buyer;run; proc sort data=appearances_buyer; by id; run; data previous_estamp; merge month_t(in=a) appearances_buyer(in=b); by id; if a; array change _numeric_; do over change; if change=. then change=0; end; run; data sole_&start_date; set previous_estamp; if estamp_buyer>=1 then first_time=1; else first_time=0; if first_time=1 then classification='Previous Residential Home Owner'; if first_time=0 then classification='First Time Buyer of Residential Property'; run; So currently, I'm changing the months at the top of the program but as discussed I would like to write a macro which would do this automatically and create a dataset at the very end.   Hopefully this makes things a little clearer to understand. ... View more

Hi Kurt,   Many thanks for your input. Unfortunately I'm still having some issues.   Part of may data steps is that we subset the dataset based on months.   data _null_; start_date=intnx('month','01AUG18'd,0,'B'); end_date=intnx('month','31AUG18'd,0,'E'); end_date1=intnx('month','31AUG18'd,-1,'E'); call symputx('start_date',start_date); call symputx('end_date',end_date); call symputx('end_date1',end_date1); run; %put &=start_date &=end_date &=end_date1; data _null_; call symputx('start_date','01aug2018'd); call symputx('end_date','01jul2018'd); run; %macro mymac; %let curr_date=&start_date; %do %until (&curr_date < &end_date); /* your code in here */ data testa; set a.analysis_dataset; run; /*Subset for your lookup month*/ data august; set testa; if &start_date <= dt_filed <=&end_date then result="match"; if result='match' then output august; run; %let curr_date = %sysfunc(intnx(month,&curr_date,-1,b)); %end; %mend; %mymac When it gets to the step to create the august dataset we're getting an issue   NOTE: Format $CRS_CYN was not found or could not be loaded. NOTE: There were 529429 observations read from the data set A.ANALYSIS_DATASET. NOTE: The data set WORK.TESTA has 529429 observations and 167 variables. NOTE: DATA statement used (Total process time): real time 15.86 seconds cpu time 4.25 seconds NOTE: Format $CRS_CYN was not found or could not be loaded. NOTE: There were 529429 observations read from the data set WORK.TESTA. NOTE: The data set WORK.AUGUST has 0 observations and 168 variables. NOTE: DATA statement used (Total process time): real time 1.42 seconds cpu time 1.40 seconds The variable dt_filed is a date variable with the following format - 04/01/2010   Have you any advise, please? ... View more

Hi Kurt,   I'm running into some error messages here. Does it relate to the piece of code here?   NOTE: Line generated by the macro variable "START_DATE". 379 "21397 ______ 77 ERROR: Invalid date/time/datetime constant "21397"d. ERROR 77-185: Invalid number conversion on "21397"d. NOTE: Line generated by the macro variable "END_DATE". 379 "18263 ______ /*Subset for your lookup month*/ data august; set testa; if "&start_date"d <= dt_filed <="&end_date"d then result="match"; if result='match' then output august; run; ... View more

data _null_; start_date=put(intnx('month',today(),0,'B'),date9.); end_date=put(intnx('month',today(),0,'E'),date9.); end_date1=put(intnx('month',today(),-1,'E'),date9.); start_yr=put(intnx('year',today(),0,'B'),date9.); call symputx('start_date',start_date); call symputx('end_date',end_date); call symputx('end_date1',end_date1); call symputx('start_yr',start_yr); run; %put &=start_date &=end_date &=end_date1 &start_yr; Could someone provide some information as how I would  change the following code start for august 2018 and work backwards.   The above starts for the first day of the current month but I would like to start my analysis in august 2018. ... View more