topic Re: Proc transpose to convert user defined formatted table data to char format fails with huge data in SAS Programming

Proc transpose to convert user defined formatted table data to char format fails with huge data

swwithsas — Thu, 16 May 2019 19:03:09 GMT

I wanted to join user defined formats table having labels with the main table on the respective columns on which that format has been applied.

To do this I want data on this main table to be formatted as char/text for the purpose of join. I tried doing transpose but it failed with no memory error. Is there any other way to do this ? The table is huge and there are many columns on which user defined formats r applied.

Re: Proc transpose to convert user defined formatted table data to char format fails with huge data

Reeza — Thu, 16 May 2019 19:18:31 GMT

Can you provide a small example that illustrates your input data set and what you want as output and where you're having issues. This is totally doable, but data specific.

Re: Proc transpose to convert user defined formatted table data to char format fails with huge data

swwithsas — Thu, 16 May 2019 21:47:38 GMT

I have tables for each user defined formats as:
1)say l1: format_name, start, label
X 0 =0
X 60 >60,<120
Similarly, l5,l47,l77....etc

2) I have list of columns on which above formats are applied on main table:
c1 c5 c47 c77.... Etc
Ie, table column c1 has been applied with l1 format etc.

Now I have main table produced after applying above formats as :
3) say tab: id, c1, c2, c3,...................., c100
1 >60,<120 abc ............

4) I need to join table tab with all format tables to collect the counts of ids and sum of balances.
So I intend to join on label values.

Re: Proc transpose to convert user defined formatted table data to char format fails with huge data

ballardw — Thu, 16 May 2019 22:17:59 GMT

@swwithsas wrote:
I have tables for each user defined formats as:
1)say l1: format_name, start, label
X 0 =0
X 60 >60,<120
Similarly, l5,l47,l77....etc

2) I have list of columns on which above formats are applied on main table:
c1 c5 c47 c77.... Etc
Ie, table column c1 has been applied with l1 format etc.

Now I have main table produced after applying above formats as :
3) say tab: id, c1, c2, c3,...................., c100
1 >60,<120 abc ............

4) I need to join table tab with all format tables to collect the counts of ids and sum of balances.
So I intend to join on label values.

Not a very clear description.

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 {i} icon or attached as text to show exactly what you have and that we can test code against.

Or provide something like this:

data table1;
   input format_name $ start $ label $ ;
datalines;
X  0  0
X  60 >60,<120
;

That will replicate your data. The way you post this
X 60 >60,<120

I can't quite tell if ">60" is a start or label or if ",<120" is part of the same start or label or a different on.

If you have "user defined formats" have they been defined in this session?

If your "tables for each user defined formats" are set up correctly, yours isn't as the variable name would be FMTNAME not format_name, you could use those sets as CNTLIN datasets on proc format to create the formats.

2) I have list of columns on which above formats are applied on main table:
c1 c5 c47 c77.... Etc
Ie, table column c1 has been applied with l1 format etc.

Where in that "list" do you show anything that links "column" C1 with I1 format? What format is C5 or C47 supposed to have?

Is C1 a variable?

Re: Proc transpose to convert user defined formatted table data to char format fails with huge data

swwithsas — Fri, 17 May 2019 09:09:52 GMT

for anyone being SAS developer, I thought basic problem definition with little illustrations, should have been enough to understnad the problem at hand.

However, I will try to explain again:

Yes the user defined formats are defined in my session and a list of columns and their user defined formats is applied on the main table data with obvious statement:

format &full_format_list;

---where, full_format_list is something like: (c1 X. c5 Y. c47 Z. ... etc)

Output is our table that looks something like the one attached: (note: it is just the small snapshot of the table containing huge data and many comuns).

format tables l1,l5,l47 etc have been created using :

proc format cntlout = &outvals

(keep = fmtname start label

So my problem is :

I need to join the main table with each of these format tables to gather the count and balance for each label value of the formats.

something like this:

select "&var_cat" as Category length=50,t2.LABEL as Categories,count(t1.acc_id) as N,t2.POU_DT,sum(BALANCE_AMT) as Balance

from main_table t1

RIGHT OUTER JOIN

l&i. t2

strip(put(t1.&&VARS&i.,32.))=t2.LABEL and t1.POU_DT=t2.pou_dt

group by t2.pou_dt,t2.FMTNAME,t2.LABEL order by t2.LABEL,t2.pou_dt asc;

but the join fails as operands on either side of = are of different formats for obvious reasons.

So I need the entire main_table to be in character format.

If there is any way other than proc transpose applied twice, please let me know. Thanks in advance!

Re: Proc transpose to convert user defined formatted table data to char format fails with huge data

ballardw — Fri, 17 May 2019 16:47:23 GMT

How about providing some very small example data, the "format tables" and the desired results.

I cannot see why any "join" is needed without a concrete example.

The example likely only needs two or three variables, the 'formats' in what ever form, 5 or 10 rows of data and desired output for the given example data.

The example PDF isn't very helpful without at least some sort of column header or row header to indicate what any of that stuff is.

BTW, MACRO variables without definitions obscure what you may actually be doing. I have seen some pretty strange things placed into single macro variables.