topic Re: How to dequote a value in SAS Programming

How to dequote a value

Pandu2 — Fri, 03 Dec 2021 07:10:39 GMT

Hi all,
I've a temp dataset whose structure is like below
sno name.
1 "Xyz":"123"
2 "gef":"abc23"
3 "rod":"124899"
4. "Xyz":"567"
5. "gef":"ijk67"
6. "rod":"800775"
Likewise it has multiple records
which are repeated what I would like to do is dequote each name variable value and put it as a separate variable, value to create a dataset which is like this
Sno Xyz gef rod
1 123 abc23 124899
2 567 ijk67 800775
Does anyone help me out on how to do this. Any help would be greatly appreciated.

Re: How to dequote a value

PeterClemmensen — Fri, 03 Dec 2021 07:27:36 GMT

So, you don't care about the "Xyz", "gef" and so on?

Re: How to dequote a value

Pandu2 — Fri, 03 Dec 2021 07:48:11 GMT

I do care about them as they must act as a columns in a dataset

Re: How to dequote a value

ChrisNZ — Fri, 03 Dec 2021 08:04:51 GMT

Like this?

data HAVE;
 input A B :$20.;
 COL=scan(B,1,'":');
 VAL=scan(B,2,'":');
 if COL='Xyz' then ROW+1;
cards;
1 "Xyz":"123"
2 "gef":"abc23"
3 "rod":"124899"
4. "Xyz":"567"
5. "gef":"ijk67"
6. "rod":"800775"
;
proc transpose data=HAVE out=WANT; 
  var VAL; 
  id COL;
  by ROW;
run;

ROW	_NAME_	Xyz	gef	rod
1	VAL	123	abc23	124899
2	VAL	567	ijk67	800775

Re: How to dequote a value

Pandu2 — Fri, 03 Dec 2021 08:08:11 GMT

Exactly but quite few changes are required in your code and output dataset please remove the name variable and also xyz,gef, rod column values must be taken from a dataset instead of entering manually cuz they're multiple records which are repeated

Re: How to dequote a value

ballardw — Fri, 03 Dec 2021 08:13:07 GMT

Suggestion: Copy a few lines, if the example you show is good, 12 or 15 should do as text from your and on the forum open a text box using the </> icon above the message window and paste the example text.

The message windows on this forum will reformat pasted text and may acquire non-printable characters making the example that you posted very likely not exactly as the source file actually is.

Any description you may have, such as the types of values, numeric or character, and the maximum lengths would be a good idea if available. Note: "123" might need to be numeric for you or character, we can't tell.

Also, were the 1 through 6 your example line numbers or do they appear in the data file, same with your "want" example.

Re: How to dequote a value

Pandu2 — Fri, 03 Dec 2021 08:17:05 GMT

Those values must be character like 123,abc23 etc. And also 1through 6 are serial numbers

Re: How to dequote a value

ballardw — Fri, 03 Dec 2021 09:28:02 GMT

@Pandu2 wrote:
Those values must be character like 123,abc23 etc. And also 1through 6 are serial numbers

So, if the 1 through 6 are in your data where to did the 3, 4, 5 and 6 go?

Character variables have length. If you don't tell what the longest expected length is for any value then you may not have the entire value represented.

Variables you don't want can always be DROPped from the data set.

The example solution posted by @ChrisNZ uses inline data because we do not have a file. Use an INFILE statement to point to your data file instead of using the datalines. Basic SAS programming.

Re: How to dequote a value

Pandu2 — Fri, 03 Dec 2021 09:34:15 GMT

I tried using infile statement but in the end the dataset seemed blank nd also the length of the character supposed to 50

Re: How to dequote a value

Kurt_Bremser — Fri, 03 Dec 2021 09:40:30 GMT

Basics first.

Is this a SAS dataset you get from somewhere without having control over it, or do you get an external (to SAS) file that you import into SAS yourself?

If the first, are the quotes actually part of the data, and not caused by some kind of format?

If the latter, we need to see an example of the source; if it is some kind of text, open the file with a text editor and copy/paste it into a code box opened with this button:

Re: How to dequote a value

Pandu2 — Fri, 03 Dec 2021 10:10:51 GMT

I've got that file which I've no control over it as it is from servers.

Re: How to dequote a value

Kurt_Bremser — Fri, 03 Dec 2021 10:13:33 GMT

So it is an external file. Please post your code that reads it.

Re: How to dequote a value

ballardw — Fri, 03 Dec 2021 10:16:07 GMT

@Pandu2 wrote:
I tried using infile statement but in the end the dataset seemed blank nd also the length of the character supposed to 50

Are there errors in the log?: Post the code and log in a code box opened with the "</>" to maintain formatting of error messages.

No output? Post any log in a code box.

Unexpected output? Provide input data in the form of data step code pasted into a code box, the actual results and the expected results. Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the "</>" icon or attached as text to show exactly what you have and that we can test code against.

Include all the code to read the file submitted along with all messages

Re: How to dequote a value

Pandu2 — Fri, 03 Dec 2021 10:22:10 GMT

Sure,
Data work.want
Infile srct dad lrecl=30000000 dlm ='[{}}';
Input js : $2000.@@;
do i =1 to countw(js, ' , ');
name =scan(js,i, ' , ');
Output;
end;
drop js i ;
run;

So, it creates a temp dataset and has multiple observations but I kept few only. Please have a look at the data which I posted above.

Re: How to dequote a value

Pandu2 — Fri, 03 Dec 2021 10:23:09 GMT

Wrong type it's dsd.

Re: How to dequote a value

Kurt_Bremser — Fri, 03 Dec 2021 10:32:52 GMT

What you posted earlier is what you think is in the dataset, not what is in the file. Open the file with a text editor and copy a few lines into a code box. I will not repeat this a third time.

Re: How to dequote a value

Kurt_Bremser — Fri, 03 Dec 2021 10:35:22 GMT

BTW your data as posted has three columns, while this code will create only one.

Re: How to dequote a value

Pandu2 — Fri, 03 Dec 2021 10:35:23 GMT

Didn't I say it's not in my control. It was on the server side I've got no access for that

Re: How to dequote a value

Pandu2 — Fri, 03 Dec 2021 10:36:24 GMT

I posted the data which has one column and I need the final dataset which should have 3 columns

Re: How to dequote a value

Kurt_Bremser — Fri, 03 Dec 2021 10:38:32 GMT

If you don't have access to it, you can't read it. If you can read it from SAS, you can download it to your desktop, either with SAS Studio or the Copy Files task in Enterprise Guide.