Accepted Solutions
Once you have the JSON data properly extracted from the CSV file you can finally turn to trying to parse it.
data tall;
set json(keep=id type rep json);
where json ne ' ';
length line row 8 name $32 value $200 term $300 record $1500;
do line=1 to countw(json,'|');
record=left(scan(json,line,'|'));
do row=1 by 1 until(term=' ');
term=scan(record,row,"[{,'}]"||'A0'x);
term=translate(term,' ','A0'x);
name=dequote(scan(term,1,':','q'));
value=dequote(scan(term,2,':','q'));
if term ne ' ' then output;
end;
end;
drop json term record;
run;
proc sort;
by id type rep line row;
run;
proc transpose data=tall out=wide(drop=_name_) ;
where name ne 'id';
by id type rep line ;
id name;
var value;
run;Looks like you also might need to do a little more cleaning. It appears some of the JSON strings have MID instead of MIDPOINT for one the timestamp variables.
@confooseddesi89 wrote:
I don't see how I can attach the .csv to a post, so I've uploaded it here
@confooseddesi89 Just drag&drop. I've done it here now because many people won't download from some link.
The following code won't return the final result you're likely after but should take you one step further. Please don't just state what's still missing but just where you need further help with.
One of the challenges with your data: The json in the data needs some clean-up to use with a json engine. The question is where all this additional quoting got added.
Could it be that the .csv you've shared is some export from a database where the json string is stored in a CLOB field? Would you in the end directly access the database? And if so: Which database?
%let source_path=C:\temp;
data step1;
infile "&source_path\dep_PA to paste sample v3 for sas forum.csv"
firstobs=2
lrecl=10000
encoding="utf-8"
termstr=crlf
dlm=','
truncover
dsd
;
length id dep 8;
format dep_date date9.;
input id dep_date:mmddyy10. dep_PA:$2000. @;
dep=1;
dep_pa=transtrn(dep_pa,"['",'[');
dep_pa=transtrn(dep_pa,"']",']');
dep_pa=transtrn(dep_pa,'""','"');
output;
input dep_date:mmddyy10. dep_PA:$2000.;
dep=2;
dep_pa=transtrn(dep_pa,"['",'[');
dep_pa=transtrn(dep_pa,"']",']');
dep_pa=transtrn(dep_pa,'""','"');
output;
run;
data step2;
set step1;
length name $32 value $40 ;
do i=1 by 1 until(name=' ');
name=dequote(scan(scan(dep_pa,i,"{{,'}]"),1,':'));
value=scan(scan(dep_pa,i,"{{,'}]"),2,':');
if name ne ' ' then output;
end;
run;
Someone above posted an actual file. So let's use that file.
The first thing that needs to be fixed it to remove the END OF LINE characters from some of the values so that each observation is only ONE LINE in the CSV file. You can use this macro: https://github.com/sasutils/macros/blob/master/replace_crlf.sas
Let's replace the LINE FEED characters with a PIPE character so we know where the line breaks used to be.
filename csv "c:\downloads\dep_PA to paste sample v3 for sas forum.csv";
filename csvfix temp;
%replace_crlf(csv encoding=any,csvfix,cr=,lf='|');Now we can read the CSV file into an actual SAS dataset.
data have;
infile csvfix dsd truncover firstobs=2 ;
input id $ dep_date :mmddyy. dep_1_PA :$1500. date_2 :mmddyy. dep_2_PA :$1500.;
format dep_date date_2 yymmdd10.;
run;Now let's parse the strings into records and then into terms and then into NAME/VALUE pairs.
data tall;
set have;
array x DEP_1_PA DEP_2_PA;
length col line row 8 name $32 value $200 term $300 record $1500;
do col=1 to dim(x);
do line=1 by 1 until(record=' ');
record=scan(x[col],line,'|');
do row=1 by 1 until(term=' ');
term=scan(record,row,"[{,'}]");
term=translate(term,' ','A0'x);
name=dequote(scan(term,1,':','q'));
value=dequote(scan(term,2,':','q'));
if term ne ' ' then output;
end;
end;
end;
drop DEP_1_PA DEP_2_PA term record;
run;Now we can transpose
proc transpose data=tall out=want(drop=_name_);
by id dep_date date_2 col line;
id name ;
var value;
run;
Note that everything is a character string, but most of those variables look like numbers to me.
You could write the file back out into an actual CSV file and let PROC IMPORT try to read it in and it will do a pretty good job with this small sample.
filename newcsv temp;
proc export data=want file=newcsv dbms=csv;
run;
proc import file=newcsv dbms=csv out=want2 ;
run;
Variables in Creation Order # Variable Type Len Format Informat 1 id Num 8 BEST12. BEST32. 2 dep_date Num 8 YYMMDD10. YYMMDD10. 3 date_2 Num 8 YYMMDD10. YYMMDD10. 4 col Num 8 BEST12. BEST32. 5 line Num 8 BEST12. BEST32. 6 dep_count Num 8 BEST12. BEST32. 7 dep_duration Num 8 BEST12. BEST32. 8 happy_count Num 8 BEST12. BEST32. 9 qual Num 8 BEST12. BEST32. 10 end_datetime Num 8 B8601DZ35. B8601DZ35. 11 date Num 8 YYMMDD10. YYMMDD10. 12 mid Num 8 B8601DZ35. B8601DZ35. 13 counter_report Num 8 BEST12. BEST32. 14 counter_nap Num 8 BEST12. BEST32. 15 counter_dep Num 8 BEST12. BEST32. 16 counter_to_eat Num 8 BEST12. BEST32.
ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.
-
3181 views
-
1 like
-
-
