DATA Step, Macro, Functions and more

print constant values assigned to column values

Accepted Solution Solved
Reply
Regular Contributor
Posts: 190
Accepted Solution

print constant values assigned to column values

[ Edited ]

data test1;
input name $15.;
cards;
sam
danny
jacob
susan
sandra
vinny
alicia
run;

data .test2;
input name $15.;
cards;
sam
dann
jhon
susan
sandy
vinny
run;

 

hello am trying to write a macro code which takes the file locations of tables & assigns libnames and takes tables from different datasets

having common columns & assigining random values to the column variable if they are in the variable parameter.these assigined values remain constant throughought the tables. 

 

for example am having 2 datasets test1 & test2 with 'name' column common in both datasets then how do I take their location in macro parameter & assign  libname to them & assign the same random values to same values & to other values in name columns in both datasets.

parameters-->indsn odsn filename variable 

random value example '088PQRSDW'

 


Accepted Solutions
Solution
‎10-05-2016 04:13 AM
Super User
Posts: 6,928

Re: print constant values assigned to column values


RTelang wrote:

@kurtBrememer this I tried but i fail to understand how do i take location and check data-sets at that location see if the column exist then assign random values...
%macro val(lib=,ds=,libname=,column=);
libname myfile "&lib";

%if %sysfunc(exist(myfile.&ds))=0 %then %do;
%if %sysfunc(exist(myfile.&ds,view))=0 %then
%do;

proc sql;
create table datasets as select distinct memname from dictionary.columns
where upcase(libname) = upcase("&lib") and upcase(name) = upcase("&column");
quit;

data _null_;
set datasets end=done;
if _n_ = 1 then call execute('data values;set ');
call execute("&lib.."!!strip(memname)!!' ');
if done then call execute(": keep &column; run;");
run;

proc sort data=values nodupkey;
by &column;
run;
%end;
%end;
%quit:
%mend val;
%val(lib='C:\Users\Desktop\Myfiles',ds=test1,column=name);

but gives error
Invalid physical name for library MYFILE.
ERROR: Error in the LIBNAME statement
.
File WORK.VALUES.DATA does not exist.


 

I hate to repeat myself, but:

 

Before putting it into a macro, do it step-by-step

 

!!!

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Super User
Posts: 6,928

Re: print constant values assigned to column values

Note: in SAS speak, "table" and "dataset" usually has the same meaning.

 

So you want a macro that accepts a library and column name as parameters, then looks for tables in that library containing the named variable, and then fills that variable with random values, but the random values need to be uniform across tables for a given observation number? What if tables have different numbers of observations?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 190

Re: print constant values assigned to column values

@Kurt yes table dataset its same srry my mistake yes i want indsn= lib= odsn= variable= where ever in the diff tables name column is there the column values must be assigined random values like these '088PQRSDW' and same values as in my data sam vinny susan are in both datasets so they will have same random values which is constant throught.
Super User
Posts: 6,928

Re: print constant values assigned to column values

[ Edited ]

Ah, I seem to see what you're up to.

You want to anonymize key values across datasets.

 

Before putting it into a macro, do it step-by-step

- set the values that are later to be macro parameters with %let (library,column)

- retrieve the dataset names from dictionary.columns:

proc sql;
create table datasets as select distinct memname from dictionary.columns
where upcase(libname) = upcase("&libname") and upcase(name) = upcase("&column")
;
quit;

Now, collect all values from all datasets:

data _null_;
set datasets end=done;
if _n_ = 1 then call execute('data values;set ');
call execute("&libname.."!!strip(memname)!!' ');
if done then call execute(": keep &column; run;");
run;

proc sort data=values nodupkey;
by &column;
run;

In a next data step, assign a random value to a new variable in values. Use a randomizing algorithm of your choice.

Depending on the distinct count of values, you might then create a value format from your values, and use that in an automatically created sequence of data steps (see how I used call execute), or you might need to use call execute to create code for each dataset that does a join and sets the random values.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 190

Re: print constant values assigned to column values

[ Edited ]

how do i simple check the diffefrent table column values at a certain location through location_parameter & assign random values to them.

Super User
Posts: 6,928

Re: print constant values assigned to column values


RTelang wrote:
how do i simple check the table column values & assign random values to them. use a lib parameter & assign the location in the macro call.

#1 Read my post

#2 You know how to use the libname statement, don't you? And how to use macro variables in program code?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 190

Re: print constant values assigned to column values

[ Edited ]

@kurtBrememer this I tried but i fail to understand how do i take location and check data-sets at that location see if the column exist then assign random values...
%macro val(lib=,ds=,libname=,column=);
libname myfile "&lib";

%if %sysfunc(exist(myfile.&ds))=0 %then %do;
%if %sysfunc(exist(myfile.&ds,view))=0 %then
%do;

proc sql;
create table datasets as select distinct memname from dictionary.columns
where upcase(libname) = upcase("&lib") and upcase(name) = upcase("&column");
quit;

data _null_;
set datasets end=done;
if _n_ = 1 then call execute('data values;set ');
call execute("&lib.."!!strip(memname)!!' ');
if done then call execute(": keep &column; run;");
run;

proc sort data=values nodupkey;
by &column;
run;
%end;
%end;
%quit:
%mend val;
%val(lib='C:\Users\Desktop\Myfiles',ds=test1,column=name);

but gives error
Invalid physical name for library MYFILE.
ERROR: Error in the LIBNAME statement
.
File WORK.VALUES.DATA does not exist.

Super User
Super User
Posts: 7,392

Re: print constant values assigned to column values

It seems, from your posts, that you are fixed on macro language.  As mentioned in various other posts, Base SAS is the key language, Macro is nothing more than a text generator.  As such, look at your Base SAS code and get that working.  Just a look at your post there you have stated what the problem is:

Invalid physical name for library MYFILE.
ERROR: Error in the LIBNAME statement  

So, strip out all the macro code, and get your code working as Base SAS, i.e. start with the code line:

libname myfile "C:\Users\rtelang\Desktop\Myfiles";

Run that actual code in your SAS interactive window, does it work, does that folder exist, do you have access to it.  Once you have that running then add in the next datastep, get that working etc. until you have correct and functioning code.  Then maybe look at adding macro parts to it.  For instance, why bother having a call execute block, if your putting all this in macro language?  You can do this very simply in Base SAS:

data _null_;
  set datasets end=done (where=(libname="<insert libname>" and name="<insert column>"));
  if _n_ = 1 then call execute('data values;set ');
  call execute(cats("<insert libname>.",memname)));
  if done then call execute("; keep <insert column name>; run;");
run;

Note the use of the code window.

Regular Contributor
Posts: 190

Re: print constant values assigned to column values

@RW9 hmm thanks for d input but my concern is in a certain location there are 3 datasets with each having name variable as column.so i want to assign random values to the orininal name column values/ now am trying to use a generic macro with parameters to take indsn,odsn,variable,location as parameter so i can check any variable to assign random values to but if the value is same in diff columns then their random value should be constant...
Super User
Posts: 17,748

Re: print constant values assigned to column values

List your steps out. 

Tackle them one at a time. 

 

First, use dictionary table to search for existence of columns. 

Second step is...,?

Super User
Super User
Posts: 7,392

Re: print constant values assigned to column values

Shouldn't matter:

Step 1 - write Base SAS which does the job

Step 2 - think about parameterising the code for macro purposes

 

Solution
‎10-05-2016 04:13 AM
Super User
Posts: 6,928

Re: print constant values assigned to column values


RTelang wrote:

@kurtBrememer this I tried but i fail to understand how do i take location and check data-sets at that location see if the column exist then assign random values...
%macro val(lib=,ds=,libname=,column=);
libname myfile "&lib";

%if %sysfunc(exist(myfile.&ds))=0 %then %do;
%if %sysfunc(exist(myfile.&ds,view))=0 %then
%do;

proc sql;
create table datasets as select distinct memname from dictionary.columns
where upcase(libname) = upcase("&lib") and upcase(name) = upcase("&column");
quit;

data _null_;
set datasets end=done;
if _n_ = 1 then call execute('data values;set ');
call execute("&lib.."!!strip(memname)!!' ');
if done then call execute(": keep &column; run;");
run;

proc sort data=values nodupkey;
by &column;
run;
%end;
%end;
%quit:
%mend val;
%val(lib='C:\Users\Desktop\Myfiles',ds=test1,column=name);

but gives error
Invalid physical name for library MYFILE.
ERROR: Error in the LIBNAME statement
.
File WORK.VALUES.DATA does not exist.


 

I hate to repeat myself, but:

 

Before putting it into a macro, do it step-by-step

 

!!!

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 357 views
  • 6 likes
  • 4 in conversation