Providing a "have" dataset that covers the different cases and showing us the desired result often helps a lot in reducing ambiguity and you will also get quicker the answer you need.
I had to make a few assumptions so not sure if what I've done will match your reality.
data have;
OrderID='12345';
ProductID='abc789, der098I';
output;
OrderID='999';
ProductID='xyz';
output;
run;
data want;
set have(rename=(ProductID=_ProductID));
length ProductID $10;
_n_terms=countw(_ProductID,',');
do _i=1 to _n_terms;
ProductID=strip(scan(_ProductID,_i,','));
output;
end;
drop _:;
run;
And just by looking at "have" what bit of information tells us that product der098I belongs to order 12345?
And that xyz is the product and not the order id but that we need to use order id 44409 here? Please explain the logic required based on the sample data provided.
If I got that right then "have" is an intermediary table that you created earlier in your process? Would it be possible to avoid "shuffling" your data earlier in the process?
"This is the table I have here 12345 has two ProductID's abc789, der098I and for 44409 it has one"
That's what you know somehow but how can someone else tell just by "looking" at the have data you shared. Can you explain the required logic? It's not obvious to me.
If you retrieve the data via an API call then how do you read this data into a SAS table? Eventually that's the place where something needs to change.
If you can please share/attach an actual Json.
If the data is confidential then you could use a text editor and change the values - but don't touch the Json structure.
Looks like rather than "finding a field" it seems like you're trying to fill in missing values with previous values.
If that's correct, here's something that will fill it in, in a new variable. If this works it's easy enough to rename and drop the old variable.
*create example data;
data have;
OrderID='12345';
ProductID='abc789';
output;
OrderID='44409';
ProductID= 'der098I';
output;
OrderID='';
ProductID='xyz';
output;
run;
*answer to question - replace HAVE with your original data set name;
data want;
set have;
*keeps the ID in cases where it's missing;
retain NewOrderID;
*if not missing then can assign it the OrderID;
if not missing(OrderID) then NewOrderID = OrderID;
run;
@Pandu2 wrote:
Actually I've got this data from an Api
Where a OrderID is the unique key and for a particular OrderID it has multiple ProductID's.
I've that data in a temporary table which is like this :
OrderID ProductID
12345 abc789
44409 der098I
Empty xyz
This is the table I have here 12345 has two ProductID's abc789, der098I and for 44409 it has one ProductID xyz.
The final table supposed to look like :
OrderID ProductID
12345 abc789
12345 der098I
44409 xyz.
I hope I cleared everything.
@Pandu2 wrote:
I'm not at all trying to fill the missing's. Please go through previous posts as well so that you can able to understand
I did read them. I don't seem to be the only one not understanding you but I'll leave it to others now.
If you are dealing with hierarchical data, like JSON, use a JSON map to map the structure to what SAS needs.
See Chris' post here for ideas: Reading data with the SAS JSON libname engine - The SAS Dummy
Rather than trying to wrangle SAS's XY layout (row/column), map the JSON tree structure so it creates the SAS layout for you.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.