topic Re: finding a field in SAS Enterprise Guide

finding a field

Pandu2 — Wed, 05 Jan 2022 12:28:11 GMT

Hi All,
I wish you a happy new year to everyone.
I have a temporary dataset which has columns like this.
OrderID ProductID

Here for a particular OrderID it may have multiple ProductID's corresponding to OrderID. But my data is like
Example: For a OrderID : 12345 it has two ProductID's : abc789, der098I.
I would like to have a final dataset should be like
OrderID ProductID
123451 abc789

123451 der098I

Re: finding a field

Pandu2 — Wed, 05 Jan 2022 12:32:38 GMT

Continuous
But I've got the dataset like
OrderID ProductID
12345I abc789
40028 der098I
As you have seen above for a OrderID 12345I it has two ProductID's so the OrderID should be same for above mentioned ProductID's but instead of that the OrderID is taking another OrderID instead of repeating the same. So, how can I achieve this feat.
OrderID ProductID
12345I abc789
12345I der098I
Please help in achieving this. Any help would be greatly appreciated. Thanks.

Re: finding a field

Patrick — Wed, 05 Jan 2022 12:40:56 GMT

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;

Re: finding a field

Pandu2 — Wed, 05 Jan 2022 12:56:30 GMT

Sorry for the inconveniences caused.
Initially I've a dataset named have which has:
OrderID ProductID
12345 abc789
44409 der098I
Xyz

The dataset I want is:
OrderID ProductID
12345 abc789
12345 der098I
44409 xyz

Re: finding a field

Patrick — Wed, 05 Jan 2022 13:06:38 GMT

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?

Re: finding a field

Pandu2 — Wed, 05 Jan 2022 13:15:25 GMT

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.

Re: finding a field

Patrick — Wed, 05 Jan 2022 15:18:18 GMT

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

Re: finding a field

Reeza — Wed, 05 Jan 2022 16:09:26 GMT

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.

Re: finding a field

Pandu2 — Thu, 06 Jan 2022 03:34:29 GMT

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

Re: finding a field

Pandu2 — Thu, 06 Jan 2022 03:37:38 GMT

The Api has json data so when I fetch that data it has these two fields named OrderID and ProductID but there's no pair for OrderID and ProductID that's the reason why it is taking another OrderID instead of repeating the same for associated ProductID's.

Re: finding a field

Reeza — Thu, 06 Jan 2022 03:54:31 GMT

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

Re: finding a field

Patrick — Thu, 06 Jan 2022 05:10:46 GMT

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.

Re: finding a field

AlanC — Thu, 06 Jan 2022 05:46:56 GMT

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.