How to dequote a value

[Pandu2]

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.

[PeterClemmensen]

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

[Pandu2]

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

[ChrisNZ]

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

[Pandu2]

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

[ballardw]

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.

[Pandu2]

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

[ballardw]

---

@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.

[Pandu2]

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

[ballardw]

---

@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-dat... 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

[Kurt_Bremser]

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:

[Pandu2]

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

[Kurt_Bremser]

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

[Pandu2]

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.

[Pandu2]

Wrong type it's dsd.