Thanks Tom, your code is working for me.  ... View more

Yeah, your assumption is so right. ... View more

Thanks for the code. The date1 & date2 variables has numerous records n the date2 value shouldn't be constant. ... View more

Please look at the newly added reply for Tom.   ... View more

I've added another parameter in this macro loop for the below code. %macro loop(date1=, date2=); Data want; Set xyz.abc_06jun2022; ( date1 parameter goes to table name )  array values id count price_05jun2022; ( date2 parameter goes to column name) Pd= catx(" - ", vname(values(whichn(price_05jun2022, of values (*)))), price_05jun2022); run; %mend loop;   Data _null_; Set work.have; * this input table has date values for date1 variable; If _n_>=1 then call execute(%nrstr(%loop( date1=' || strip(date1) ||'))'); run; ... View more

Thanks for the code. My date1 variable in that table is of date type.  And the code inside the macro loop has some tables which has dates embedded in it and some columns as well. Lemme show you my code. Data want; Set xyz.abc_06jun2022; array values id count price_05jun2022; Pd= catx(" - ", vname(values(whichn(price_05jun2022, of values (*)))), price_05jun2022); run; Here the column name for price_05jun2022 keeps on changes on daily basis. For instance for today's date the column name will be price_110ct2022. This is the code which should loop in that loop macro. When comes to your code it's working fine for the input table name of the above code but not working for the column name i.e price_date2. ... View more

Thanks for replying me, the code which I posted was working for my previous projects but when comes to this project the date1 value should as-is cuz under the macro loop code I've a bunch of tables which takes that date1 variable value as input cuz those tables has date embedded at the end.  ... View more

I just posted how the way it looks like and I kinda agree with you that it doesn't follow a specific pattern. ... View more

Log:   NOTE: There were 3417992 observations read from the data set work.have. NOTE: The data set work.want has 1 observations and  10 variables. NOTE: Compressing data set work.want increased size by 100.00 percent. Compressing is 2 pages; un- compressed would  require 1 pages. NOTE: DATA statement used(Total process time).  Only these set of lines I've it on my sas log. ... View more

Here's the original format of my json data: { """BatchNo"":""001"""" "Keys": [ { """Goodsname"":""Nikeshoes"""" """Quantity"":""3"""" """Infotype"":""Sneakers"""" } { """Goodsname"":""pants"""" """Quantity"":""5"""" """Infotype"":""Trackpants"""" """Newly"":"""""" """Infotype"":""RAQ"""" """TypeId"":""109"""" """Cause"":""Broken"""" """Quantity"":""0"""" } }{ """BatchNo"":""009"""" "Keys":[ { """Goodsname"":""Nikeshoes"""" """Quantity"":""3"""" """Infotype"":""Sneakers"""" }{ """Goodsname"":""pants"""" """Quantity"":""5"""" """Infotype"":""Trackpants"""" } ] } { """BatchNo"":""011"""" "Keys":[ { """Goodsname"":""Jackets"""" """Quantity"":""3"""" """Infotype"":""topwears"""" } """Newly"":"""""" """Infotype"":""RAQ"""" """TypeId"":""100"""" """Cause"":""Broken"""" """Quantity"":""0"""" ] } ... View more

My Apologies, it didn't work. It has only fieldname and fieldvalue. ... View more

Actually when a new batchNo comes that's the sign of previous BatchNo group ends. That's how I figured out after doing some analysis how the group ends, otherwise our business and I doesn't know how it ends cuz the complexity of data and not following any specific pattern. ... View more