topic Newbie here! Deleting rows in public SAS Macro in SAS Programming

Newbie here! Deleting rows in public SAS Macro

moomoo11 — Thu, 31 May 2018 00:18:56 GMT

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!

Re: Newbie here! Deleting rows in public SAS Macro

Tom — Thu, 31 May 2018 01:07:29 GMT

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.

Re: Newbie here! Deleting rows in public SAS Macro

SuryaKiran — Thu, 31 May 2018 02:38:47 GMT

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;