Need help assigning macro value to 2D array

[imdickson]

Hi Guys, my current task is to first assign 3 different columns into 3 different macro value array, then build a 2D array that will contains sourcetable+column and targettable+column.

For example, I will have 3 different macro variable called &sourcetable, &targettable and &column.

As you can see from the name itself, each of it stores its value. &sourcetable and &targettable are having 37 rows each while &column has 1000 rows.

Here is the thing. I would like to assign the &column to both &sourcetable and &targettable.

1 table can have multiple columns.

My colleague gave me a tips to do in this way:

            table 1               table 2                               table 3

array1-column1      array2-column6          array3-column7

array1-column2      array2-column8          array3-column9

array1-column3      array2-column10        array3-column11

The very main source table is the dictionary table where i filter the data by only getting the Staging layer tables. In this table, it will have the table name and column name.

I do not have much experience in macro programming/array. I would like to seek for guides and advice here.

Thanks alot.

[RW9]

Please follow guidance you will see when posting a new question.  Post example test data in the form of a datastep, what the output should look like and a breif explanation of the logic.

It sounds like, from teh bit below, you should be able to do this with a simple do loop (to go across) and a controlled output statement to jump to the next row.  Test data and output will help us understand.

[ballardw]

Why?

The macro language is in general not intended for actual data value processing and this question sounds like that is what you are attempting to do.

So how will you actually be using those macro elements after they are created?

[Tom]

It will help if you explain the problem more fully, with a worked example of input and expected output.

So first explain your input data and give a small example (in the form of a data step that others can run).

Then explain your expected output data and give a example (preferable the result of processing the input example) also in the form of a data step.

Then explain how the input data is getting into the macro variables. You can show what values you want to get into which macro variables by just showing some example %LET statements, even if you will use some other method for generating the macro variables.

Since macro code (including macro "arrays") are only useful for how they are used to generate SAS code you need to also explain what SAS code you want to generate and show what parts of it are the parts that will vary based on the macro variables.

And if you have already tried to do the job you can post your attempt and explain it what ways it didn't work.  Did it generate SAS errors or just not generate the data in the form you need?

[imdickson]

Hi everyone. Thanks for the feedback.

Let me explain it.

I am developing ETL for a project and the question that i asked is part of the small programme in staging job.

The thing is, there are many tables containing different columns. As per advise from my seniors, they want me to develop a 2D array to loop through the table name and its columns each time. Later on, we will use it to do unit conversion based on the user input selection on unit because there are many unit of measurement for user to choose. Each different columns will have different logic to do conversion based on the unit that user input. Hence, very much appreciated if there is anyone that can give further advice.

As for now, I have already made it to loop through all tables BUT the thing is im hitting error "column not found in xxx table" across all tables. It means the select statement is choosing the same column across all table. I am still finding a workaround for it to loop properly.

Another question, is there a way to upload sas dataset here in this forum for you all to have a look at my source data?

[RW9]

You can upload data here as plain text following this post:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

Post it in using the {i} button found above the post area.

So, you need to do some processing over various datasets/columns.  Well, one way of looking at this problem is thus:

%macro TheCode (inds=,var=);
   ...
%mend TheCode;

data _null_;
  set sashelp.vcolumns (where=(libname="WORK" and memname in ("ABC",DEF") and substr(column,1,3)="VAR"));
  call execute(cats('%TheCode (inds=',catx('.',libname,memname),',var=',name,');'));
run;

What the above will do is first filter the metadata vcolumns dataset (there is vtables for tables, and they are the same as the related dictionary tables) for your criteria - in the above example I am only looking for dataset ABC and DEF in WORK, with columns like VAR.  For each returned row (i.e. each variable found) I generate out a call to the macro with the given parameters.  The macro then does the necessary processing.  With this method you use the intrinsic functionality of a dataset to loop over the given data

[imdickson]

Hey everone! After rounds of testing and testing and testing, i did a macro with 2 loops. Basically what it does is to get the table name and then the columns belong to its table.

Now, I would like to substitute these 2 array into a SQL Statement.

Now i have 2 macro array:

columnarray - stores all column

tblnm - stores all table

so here it is :

Proc SQL;
select &&columnarray&k
from SOURCETABLE
where TABLENAME = &tblnm;
quit;

However, the select statement can have more than 1 variable, so how do i overcome the comma issue if there is more than 1 column in 1 loop?

Need your kind advice.

[RW9]

Well, the simplest way is to avoid using macro as I mentioned above.  SASHELP already has tables which hold the metadata for each table/column in SAS.  I provide some code below to show how to do this.  The question is however why you want to create a duplicate dataset - presumably with fewer columns.  IF you can show the logic of it then I will simplfy, but to create an exact copy dropping var1 and var2 from datasets abc and def which reside in work I would do:

data _null_;
  set sashelp.vtable (where=(libname="WORK" and memname in ("ABC",DEF")));
  call execute(cats('data copy_',memname,'; set ',memname,' (drop=var1 var2); run;'));
run;

The coding is far simpler and can be modified with very little effort to your needs.

[imdickson]

Hi, thanks for the advice. However, my intention is not just to display the filtered dictionary table. I am developing ETL jobs and this programme is going to first read from dictionary table, assigning into 2D array and then later on, i will pick from source tables(multiple) to join with filtered dictionary table to get the unit of measurement to do conversion.

I will further explain my task when im home later tonight as I am rushing my task urgently. However, I do hope that any experts here can advice me. Right now, I need another advice on:

I have a table called 'ReadyForConversion' that stores the tablename, originalvalue(sample data, height, length, weight, fat)  and originalunit(sample data.weightUOM, heightUOM, lengthUOM, fatUOM) of measurement. 1 table will have multiple original value and original unit of measurement. 

However, the very source tables have those mentioned columns in column based.

I want to convert into select statement: select weight, height, length, fat, weightUOM, heightUOM, lengthUOM, fatUOM from source .

Is there a way to do it?

[RW9 · Accepted solution]

You can use:

select COL
into   :LIST separated by ','
from   YOUR_TABLE

Although once again I will say you heading down a complicated, messy and hard to maintain coding path.

[imdickson]

Thanks alot. That is one of the way to assign to macro array list, then by using loop to generate the dynamic select statement.