topic Re: print constant values assigned to column values in SAS Programming

print constant values assigned to column values

RTelang — Mon, 26 Sep 2016 06:44:46 GMT

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'

Re: print constant values assigned to column values

Kurt_Bremser — Mon, 26 Sep 2016 07:08:09 GMT

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?

Re: print constant values assigned to column values

RTelang — Mon, 26 Sep 2016 07:17:23 GMT

@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.

Re: print constant values assigned to column values

Kurt_Bremser — Mon, 26 Sep 2016 07:32:02 GMT

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.

Re: print constant values assigned to column values

RTelang — Mon, 26 Sep 2016 10:03:56 GMT

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

Re: print constant values assigned to column values

Kurt_Bremser — Mon, 26 Sep 2016 08:07:02 GMT

@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?

Re: print constant values assigned to column values

RTelang — Mon, 26 Sep 2016 10:21:51 GMT

@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.

Re: print constant values assigned to column values

RW9 — Mon, 26 Sep 2016 10:15:10 GMT

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.

Re: print constant values assigned to column values

RTelang — Mon, 26 Sep 2016 10:27:33 GMT

@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...

Re: print constant values assigned to column values

Reeza — Mon, 26 Sep 2016 10:45:10 GMT

List your steps out.

Tackle them one at a time.

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

Second step is...,?

Re: print constant values assigned to column values

RW9 — Mon, 26 Sep 2016 10:53:20 GMT

Shouldn't matter:

Step 1 - write Base SAS which does the job

Step 2 - think about parameterising the code for macro purposes

Re: print constant values assigned to column values

Kurt_Bremser — Mon, 26 Sep 2016 11:36:11 GMT

@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

!!!