cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Priyanka2019
‎10-22-2019
  • 11 Posts
  • 1 Likes Given
  • 0 Solutions
  • 0 Likes Received

Re: Parsing JSON string stored in parameter column

by Calcite | Level 5 in SAS Data Science
‎10-15-2019 12:10 PM
‎10-15-2019 12:10 PM
Also some obs are strings in this ... View more

Re: Parsing JSON string stored in parameter column

by Calcite | Level 5 in SAS Data Science
‎10-15-2019 12:07 PM
‎10-15-2019 12:07 PM
Tom this makes sense system shouldnt allow it i didnt think through, ratecnt wasnt working in do while loop but will just recheck this once and confirm back. Have one more query regarding survey parsing is there where Request response:value1,obs2, obs 3 etc, ans:value5 List, ques no - 1.2 , Cu response =bcx, repeated for multiple qs are there. Was wondering same above logic will work without array n using wordcount and scan. Any Thoughts...many curly braces in between for a single q n ans in obs as json ... View more

Re: Parsing JSON string stored in parameter column

by Calcite | Level 5 in SAS Data Science
‎10-15-2019 07:20 AM
‎10-15-2019 07:20 AM
I removed array only and added another step for parsing thanks for your solution ... View more

Re: Parsing JSON string stored in parameter column

by Calcite | Level 5 in SAS Data Science
‎10-15-2019 06:41 AM
‎10-15-2019 06:41 AM
Ratecnt defined in above post was giving count for each row. But array was to be defined for each row based on Number of observations each row has. How to use ratecnt defined in proc sql for each row in do while. If i take max in proc sql n pass to macro it is constant for all rows when each row has different number of obs. The *into* clause uses only 1 value from whole table for assigning. ... View more

Re: Parsing JSON string stored in parameter column

by Calcite | Level 5 in SAS Data Science
‎10-14-2019 10:34 PM
‎10-14-2019 10:34 PM
This i thought of but then for each array i will need to calculate number of actual populated elements for do while loop because max works for overall table for eg comes 229 but some rows have as small as 98 name/ value elements. ... View more

Re: Parsing JSON string stored in parameter column

by Calcite | Level 5 in SAS Data Science
‎10-14-2019 09:36 AM
‎10-14-2019 09:36 AM
One additional query to this I will need to capture all these tokens as an array so I can search the ones I need. Also these lines are just part of more than 15K row of data. Suppose I have id=101, parameter{<datalines shared earlier>} to parse now if I get count of words and want to create array where size is dynamically captured based on the tokens present in each dataline. Each row will have different count of sub rows in parameter.  I was able to get count as a table column in proc sql :- (count(parameters,'}')*12 as ratecnt But problem is I want to define array defined on ratecnt for each row of data. But only if I take max of above and capture using into macro variable it allows me to create dynamic array. Please help me define proc sql or sas dynamic array defined based on ratcnt field captured in every row of data. Below code doesn't run but on these lines.   array parsed_vars $ 150 new_var1-new_var%eval(ratecnt); i=1; do i=1 to ratecnt; parsed_vars(i) =scan(parameters, i, "}"); i+1;*/ ... View more

Parsing JSON string stored in parameter column

by Calcite | Level 5 in SAS Data Science
‎10-09-2019 04:45 AM
‎10-09-2019 04:45 AM
I am trying to parse below json string from parameters column in table 1:- "{""creditUnionValue"":null,""intergroupVisibility"":""READONLY"",""notes"":null,""intergroupValue"":""3.25*"",""type"":""Single Rate"",""needVerification"":""true""}, {""creditUnionValue"":null,""intergroupVisibility"":""READONLY"",""notes"":null,""intergroupValue"":""2.87*"",""type"":""Joint Rate"",""needVerification"":""true""},{""creditUnionValue"":null,""intergroupVisibility"":""READONLY"",""notes"":null,""intergroupValue"":"""",""type"":""Joint Dev"",""needVerification"":""false""},{""crediting"":null,""intergroupVisibility"":""READONLY"",""notes"":null,""intergroupValue"":""40000"",""type"":""Benefit Maximize"",""needVerification"":""false""},{""crediting"":null,""intergroupVisibility"":""READONLY"",""notes"":null,""intergroupValue"":""11"",""type"":""Termination "",""needVerification"":""false""},{""crediting"":null,""intergroupVisibility"":""READONLY"",""notes"":null,""intergroupValue"":""22"",""type"":""Eligibility Age"",""needVerification"":""false""},{""crediting"":null,""intergroupVisibility"":""READONLY"",""notes"":null,""intergroupValue"":""UNL"",""type"":""Term Of Insurance"",""needVerification"":""false""},{""crediting"":null,""intergroupVisibility"":""READONLY"",""notes"":null,""intergroupValue"":""7501"",""type"":""Benefit Amount"",""needVerification"":""false""},{""crediting"":null,""intergroupVisibility"":""DEFAULT"",""notes"":null,""intergroupValue"":"""",""type"":""Delinquent Payment"",""needVerification"":""false""}" Tried Coding as below:-   select max(count(parameters,'}'))+2 into :maxelements from table1;   data want; set have; array parsed_vars $ 150 new_var1-new_var%eval(&maxelements); i=1; do i=1 to &maxelements; parsed_vars(i) =scan(parameters, i, "}"); i+1; end; run;   In the result am getting alternate row of data in new_var1 then new_var3, new_var5 all even ones are blank which is a problem. So I am getting only 5 of 9 rows.   ... View more

Re: Truncation issue in proc sql when picking large strings

by Calcite | Level 5 in SAS Programming
‎09-27-2019 01:06 AM
‎09-27-2019 01:06 AM
Thanks Saskiwi and Reeza was setting it at wrong place. It is resolved now. ... View more

Re: Truncation issue in proc sql when picking large strings

by Calcite | Level 5 in SAS Programming
‎09-27-2019 01:04 AM
‎09-27-2019 01:04 AM
It worked thank you ... View more

Re: Truncation issue in proc sql when picking large strings

by Calcite | Level 5 in SAS Programming
‎09-26-2019 10:41 PM
‎09-26-2019 10:41 PM
Thanks for the response but it seems it has restriction to exactly what i need. On sql server column is ddfined as varchar and need to use while creating sas dataset. Taken from link:- Restrictions: This option applies when you retrieve, append, and update rows in an existing table. It does not apply when you create a table. This option is ignored for CHAR and VARCHAR2 data types. ... View more

Truncation issue in proc sql when picking large strings

by Calcite | Level 5 in SAS Programming
‎09-26-2019 10:05 PM
‎09-26-2019 10:05 PM
In SAS 9.04 version when i am trying to select a column in table on sql server 2014 which contains json string 17 k long as is, it is truncating to 1000-1180. I tried giving length and format in select query eg Proc sql Create table t1 as select busobj length=15000, format = $15000. From table1; Quit; It seems this creates target sas column with size 15k in output data but actual string is trucated at approx 1173. Being different for each row picked but in similar range. Could this be due to sas db connector, server setting on sas or sql server please guide. On sql server the string is 17 k as checked ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎10-22-2019 11:17 AM
Likes given to
View All
Quick links