DATA Step, Macro, Functions and more

Newbie here! Deleting rows in public SAS Macro

Reply
Frequent Learner
Posts: 1

Newbie here! Deleting rows in public SAS Macro

Hi! I am a grad student trying to analyze some food frequency questionnaires using an existing tool and publicly available codebook/SAS macro. I have only used SAS a handful of times and never for something like this.

 

Basically, what I am trying to do (for starters) is delete the data on the SAS file I downloaded from NHANES website so I can put my data in instead. I can't even figure out how to do that. How do I delete the data in the following SAS file without messing up anything else? I coded by data in a separate excel sheet the same way the codebook (see below) codes it, but am stuck on deleting all of the rows of sample rows (over 600 samples) in the below file. My study only has 37.

 

Here is a link to the codebook: https://wwwn.cdc.gov/Nchs/Nhanes/2003-2004/FFQRAW_C.htm

Here is a link to the SAS file I am trying to use: https://wwwn.cdc.gov/Nchs/Nhanes/2003-2004/FFQRAW_C.XPT

 

Thanks so much in advance!

Super User
Super User
Posts: 8,068

Re: Newbie here! Deleting rows in public SAS Macro

If you have data in an Excel you can just read that into SAS. You could use PROC IMPORT or just use the new XLSX libref engine.

Not sure why you would need to reference any other SAS dataset to do that.

Valued Guide
Posts: 589

Re: Newbie here! Deleting rows in public SAS Macro

First you need to convert the XPT file to SAS Dataset. 

 

/* Convert XPT to SAS Dataset */
libname SAS_DS 'E:\SASUniversityEdition\SandBox\Datasets';
libname xptfile xport 'E:\SASUniversityEdition\SandBox\Files\Input\FFQRAW_C.XPT' access=readonly;

proc copy inlib=xptfile outlib=SAS_DS;
run;

There are several ways to delete rows from a dataset depending on how you want to delete them, one way is as follows

PROC SQL;
delete from SAS_DS.FFQRAW_C
where WTS_FFQ is null;
quit;

Also there are several ways to insert data like: 


/* Insert values manually */
proc sql;
insert into SAS_DS.FFQRAW_C
values (1,2,3,4,.....)
values (2,3,4,5,5....);
quit;

/* Insert values from existing table */
proc sql;
insert into SAS_DS.FFQRAW_C
select * from another_table;
quit;
Thanks,
Suryakiran
Ask a Question
Discussion stats
  • 2 replies
  • 104 views
  • 0 likes
  • 3 in conversation