DATA Step, Macro, Functions and more

Macro with do loop and proc sql

Reply
Contributor
Posts: 67

Macro with do loop and proc sql

Hi All,

 

I have around 34 datasets in one library and out of 34 , 15 datasets containing variables ending with "TESTCD" or "TEST".I have other library where in one dataset i have corresponding information .I want to merge datasets variables from one library with corresponding  information .

 

Below is my code and i have to execute the code manually by passing all dataset and variable names.My requirement is to do it dynamically.

 

The Variable name example is LBTESTCD/LBTEST where first two letters is the source dataset name.

 

/**/
/*proc import datafile="Z:\Novella\TG_Therapeutics\UTX-IB-104_NV100609_SDTM\Biostatistics\Documentation\Specifications\SDTM\SDTM Terminology.xls"*/
/* out=check dbms=xls replace;*/
/*run;*/
OPTIONS SYMBOLGEN MPRINT MLOGIC;
%macro try(domain= , var= );

proc sql;
create table x as select distinct &var. as &var. from
sdtm.&domain.
where &var. ne " ";
quit;

proc sql NOPRINT;
select code into : codelist_code from check where cdisc_submission_value="&VAR.";
quit;
%let codelist_code=&codelist_code;
/*%put &code;*/
data y;
set x;
cdisc_submission_value="&VAR";
codelist_code="&codelist_code.";
run;
PROC SQL;
CREATE TABLE FINAL AS SELECT a.&var. , b.*
FROM Y A LEFT JOIN
CHECK B
ON A.codelist_code=B.CODELIST_CODE
AND a.&var.=B.CDISC_SUBMISSION_VALUE
ORDER BY 1;
QUIT;
data &var.;
length term $40;
set final;
id="&VAR.";
name=codelist_name;
nci_codelist_code=codelist_code;
data_type="text";
order=_n_;
term=&var.;
nci_term_code=code;
decoded_value=NCI_PREFERRED_TERM;
KEEP ID NAME NCI_CODELIST_CODE DATA_TYPE ORDER TERM NCI_TERM_CODE DECODED_VALUE;
run;
%mend;
%try(domain=EG , var=EGTESTCD );
%try(domain=EG , var=EGTEST );
%try(domain=VS , var=VSTESTCD );
%try(domain=VS , var=VSTEST );
%try(domain=LB , var=LBTESTCD );
%try(domain=LB , var=LBTEST );
%try(domain=RP , var=RPTESTCD );
%try(domain=RP , var=RPTEST );
%try(domain=TR , var=TRTESTCD );
%try(domain=TR , var=TRTEST );
%try(domain=TU , var=TUTESTCD );
%try(domain=TU , var=TUTEST );
DATA ALL;
SET EGTESTCD EGTEST VSTESTCD VSTEST LBTESTCD LBTEST RPTESTCD TRTEST TUTESTCD TUTEST;
RUN;

 

Regards.

Super User
Super User
Posts: 9,842

Re: Macro with do loop and proc sql

Wh%T IS thi% Ma%% of code?

 

Post a question by first providing some example test data, in the form of a datastep using a code window (its the {i} above post area).  Then show what output should look like.  

 

Explain what your problem is, why are you trying to merge several metadata datasets of CDISC models?  Are you checking that TESTCD is consistent across all these?  If so a simpler method would be to set all the metadata into one dataset (i.e. one after the other) then do a proc sort nodupkey dupout=, the dupout dataset will then contain any ones which have different information.  But as you can see I am just guessing what your trying to do.

Contributor
Posts: 67

Re: Macro with do loop and proc sql

Hi,

 

Thanks for reply.

 

I want codelist code values which i have used for cdisc domain creations to be place in one excel sheet along with NCI preferred term.

 

For that, i have imported all specifications in sas and created one dataset having domain list along with list of variables attached with codelists.

 

so as now, i got everything in one file, i think this can be achieved by proc sql.

 

Although, please guide if you have any other workaround to get it.

Super User
Super User
Posts: 9,842

Re: Macro with do loop and proc sql

Please show some examples.

Contributor
Posts: 67

Re: Macro with do loop and proc sql

Hi,

 

I have uploaded example.xlsx .I need to append study data of all variables where codelist has been used.

 

Please review and guide if this is clear now, or should i need to provide other clear example.

Super User
Posts: 13,941

Re: Macro with do loop and proc sql

I also can't really figure out what your are attempting in a short period of time but have a suggestion:

 

In your Proc SQL at the start of the macro Try you have this bit of code:

where &var. ne " ";

 

You would be much better off to use

where not missing(&var)

Reasons: Your existing code will have problems with a variable that is numeric but the not missing will return the correct result for character and numeric variables.

 

Another suggestion related to this website: Paste code and log items into a code box opened with the forum menu {I} icon to preserve the formatting of code and any log messages. If you use any code formatting you should see the difference in you post compared with your actual code. If you do not use any code formatting such as indentation to indicate procedure start/end, loop start/end and such, then time to learn as a consistent style greatly helps debug some common problems.

Super User
Super User
Posts: 8,279

Re: Macro with do loop and proc sql

[ Edited ]

draroda wrote:

I have around 34 datasets in one library and out of 34 , 15 datasets containing variables ending with "TESTCD" or "TEST".I have other library where in one dataset i have corresponding information .I want to merge datasets variables from one library with corresponding  information .

Regards.


It sounds like you have a libref, call it STUDY1, that points to a library with 34 datasets. You want to find the variables in that library with names of the format XXTESTCD or XXTEST.  Just query the metadata.  You can use the DICTIONARY views with PROC SQL.

proc sql ;
create table varlist as 
select memname,name
from dictionary.columns
where libname='STUDY1'
  and (upcase(name) like '__TESTCD'
    or upcase(name) like '__TEST')
;
quit; 

Now what do you want to do with that list?

Contributor
Posts: 67

Re: Macro with do loop and proc sql

Hi Tom,
Just before few minutes,I have uploaded excel sheet with dummy data and
expected output.

Can you please look into same.

I think it mention my requirements?
Super User
Super User
Posts: 8,279

Re: Macro with do loop and proc sql

If you have sample data post it as data steps.

You need to explain WHY you want the given output.  For example from your initial question I could not tell if you just wanted to find the metadata on the variables, the values of the variables.  Or why you wanted whatever it was or how you intended to use it once you had it. Is this for a report? To drive some other programming step?

Contributor
Posts: 67

Re: Macro with do loop and proc sql

I have data in excel sheet only.

I want this output for reporting purpose as I want code values along with
only for those values which are used in study else I have entire database
in excel sheet where I have all values.
Super User
Super User
Posts: 8,279

Re: Macro with do loop and proc sql

It doesn't matter if your data is in Excel or on the moon, if you want people to help you take the time to type up a simple data step that sets up sample data so that they can show you how to deal with it.

 

Now it sounds like your question is:

I have metadata that contains all of the possible values and decodes. Essentially a set of format definitions. And I want a report that shows only the values that actually appear in my data.

 

To answer this question with actual code we need to know the table (or tables) that contain the code/decode values.  So post an example with the key variables and enough example observations so that you have some codes that are not present in the data (that you will also post).  We also need to have example of one or more tables that you want to test.  And finally what format to you want the report to appear in?  Do you want it listed separately by dataset? By codelist type?  

 

For example you might have this codelist metadata.

 

data codes ;
  length codelist $32 code 8 decode $50 ;
  infile cards dsd truncover ;
  input codelist code decode ;
cards;
TESTCD,1,Amylase
TESTCD,2,Glucose
TESTCD,3,Microalbumin
;

You might have the table metadata that links a codelist to a variable.

 

 

data metadata ;
  length memname name codelist $32 ;
  infile cards dsd truncover ;
  input memname name codelist;
cards;
LABS,LABCODE,TESTCD
;

Then you could have this actual data.

 

 

data labs ;
  length id $10 labcode 8 ;
  infile cards dsd truncover ;
  input id labcode ;
cards;
1,1
1,2
;

So for that test perhaps you want your report to exclude the code/decode for the lab test Microalbumin.

 

Ask a Question
Discussion stats
  • 10 replies
  • 406 views
  • 0 likes
  • 4 in conversation