Looping through variables in several dataset to add vlaue format

[Frank_johannes]

Hi all, 

First of all, I'm transferring several SAS files from one folder to another within a call execute syntax based on an 'X' in a variable called "select" in an excel file:

%macro sas;
data __null_;
set tables;
call execute('data out.'||strip(Tabeller)||';
set obk.'||strip(Tabeller)||';
run;');
run;
%mend;

This code works fine! 

But my problem is that I have a custom format library that has been imported via proc format cntlin=work.formats syntax. Every format name is mostly the same as the variablenames in each imported SAS file except from an underscore prefix in each dataset imported via above code. 

Basically I'm trying looping through each variable in each dataset and add value format to it if there is a match?

Kind regards 

Frank 

[Frank_johannes]

underscore suffix*
Tabeller Select
Table 1 x
Table 2
Table 3 x
Call execute exports Table 1 and Table 2 from sas folder to another.

[PaigeMiller]

---

@Frank_johannes wrote:

But my problem is that I have a custom format library that has been imported via proc format cntlin=work.formats syntax. Every format name is mostly the same as the variablenames in each imported SAS file except from an underscore prefix in each dataset imported via above code. 

Basically I'm trying looping through each variable in each dataset and add value format to it if there is a match?

---

You don't specifically say, but are the format assigned to the variables in library OBK? If so, I don't think any looping like this is needed. Formats don't have to be assigned to variables in the new data sets in the OUT library, the formats are carried over from the data set in the OBK library. All you have to do, when using the new data sets in the OUT library, is to make sure the custom format library is available to SAS when the OUT library is used.

As far as your looping shown to copy data sets from one library to another library using CALL EXECUTE, even that looping is not needed.

proc copy in=obk out=out memtype=data;
run; quit;

[andreas_lds]

This does not answer your question, but if you have to copy multiple datasets from one library to another, using proc copy is much faster than a data step.

Idea to solve the problem:

- extract the relevant format from sashelp.vformat (libname = 'WORK', memname = 'FORMATS')

- process sashelp.vcolumn (libname = 'OBK'), load the extracted information into a hash object, build the name the format should have an verify that it exists in the hash, use call execute with proc datasets to attach the format.

[ballardw]

Where did you write the resulting Format catalog when you used "proc format cntlin=work.formats syntax".

If you are going to use this frequently then the formats should be in a permanent library  and when ever you are using that library the SAS system option FMTSEARCH should be updated to include that library. The FMTSEARCH option tells SAS where to look for format definitions and in what order (if you have more than one format catalog with the same format name the first format of that name will be used). Likely you would want to either insert (add at the front of the search path) or append (add to the end of the search path) where to find those formats.

To list specific search order for formats that appear in catalogs named Formats in each library

options fmtsearch=(work thislib thatlib);

to insert at the front of the list or append to the end.

options insert=(FMTSEARCH=thelib); 
options append=(FMTSEARCH=thatlib);

If your format catalog has a name other than Formats then use the libname.catalog syntax instead of just the libname.

The format catalog does not have to be in the same library as the data but does have to be in a library that the current SAS session can access.

Or place the CNLIN data set in a permanent library, i.e. not the WORK library, and rerun the Proc format code in every session.

[Reeza]

It seems like your code is not related to the question of applying a format. 

Here is my recommendation:

• Obtain a list of format names from catalog - use sashelp.vformat
• Obtain a list of of variable names from each data set - use sashelp.vcolumn
• Clean up the names from Step1
• Merge the two data sets
• Then loop through and apply the formats - I would recommend PROC DATASETS via CALL EXECUTE

---

@Frank_johannes wrote:

Hi all, 

First of all, I'm transferring several SAS files from one folder to another within a call execute syntax based on an 'X' in a variable called "select" in an excel file:

%macro sas;
data __null_;
set tables;
call execute('data out.'||strip(Tabeller)||';
set obk.'||strip(Tabeller)||';
run;');
run;
%mend;

This code works fine! 

But my problem is that I have a custom format library that has been imported via proc format cntlin=work.formats syntax. Every format name is mostly the same as the variablenames in each imported SAS file except from an underscore prefix in each dataset imported via above code. 

Basically I'm trying looping through each variable in each dataset and add value format to it if there is a match?

Kind regards 

Frank 

---

[PaigeMiller]

---

@Reeza wrote:

It seems like your code is not related to the question of applying a format. 

Here is my recommendation:

• Obtain a list of format names from catalog - use sashelp.vformat
• Obtain a list of of variable names from each data set - use sashelp.vcolumn
• Clean up the names from Step1
• Merge the two data sets
• Then loop through and apply the formats - I would recommend PROC DATASETS via CALL EXECUTE

---

 I'm still not seeing the need for looping across variables in @Reeza 's suggestion.

Something like this, starting with the "merge two data sets"

proc sql noprint;
    select distinct cat(trim(name),' ',trim(fmtname),'.') into :addformat from merged_data_set;
quit;
proc datasets library=OUT;
    modify datasetname;
    format &addformat;
run;
quit;

But then you would have to loop across data sets.

[Reeza]

The looping is probably for the 'several dataset' as it's not a single data step. Could be a data step loop though.