topic Re: SAS Assignment in New SAS User

SAS Assignment

jk101564 — Mon, 08 Oct 2018 19:51:47 GMT

Does anyone know how to do this task?

Write a SAS program to access the Microsoft Excel spreadsheet.

Create a comma-delimited raw data file to store information that includes sex, categories, and number of individuals in each category.

Re: SAS Assignment

Kurt_Bremser — Mon, 08 Oct 2018 06:10:40 GMT

Please post the code you already have; post the log if you have problems, and describe where your results do not meet your expectations.

Re: SAS Assignment

Kurt_Bremser — Mon, 08 Oct 2018 07:50:21 GMT

First, save the whole shambles to a csv file, so that you can take control over the import process (especially where you start to read, and how you name the columns). Read that with a suitable data step.

Then, in a further data step, retain a variable with which you keep the sex, and use a subsetting if to only keep observations that meet your criteria.

For the last part, you can once again write a data step, or use proc export.

Re: SAS Assignment

jk101564 — Mon, 08 Oct 2018 19:52:24 GMT

I am new to the SAS and dont know much about it. i had the one assignment was really simple but then this that i dont know much about. If you can help me that about be appreciated. I could come up with this but it is not working and i have to use the excel file and output should be in .CSV file. Right now the output is the whole excel file. I have attached the file.

PROC IMPORT out= nyfile
DATAFILE = "/home/myfolder/nyfile.xlsx"
DBMS = XLSX REPLACE;
GETNAMES = no;
DBSASLABEL = NONE;
RUN;

Here is the logs

1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

71 PROC IMPORT out= nyfile

72 DATAFILE = "/home/myfolder/nyfile.xlsx"

73 DBMS = XLSX REPLACE;

74 GETNAMES = no;

75 DBSASLABEL = NONE;

76 RUN;

NOTE: One or more variables were converted because the data type is not supported by the V9 engine. For more details, run with

options MSGLEVEL=I.

NOTE: The import data set has 97 observations and 5 variables.

NOTE: WORK.NYFILE data set was successfully created.

NOTE: PROCEDURE IMPORT used (Total process time):

real time 0.01 seconds

user cpu time 0.01 seconds

system cpu time 0.00 seconds

memory 2660.21k

OS Memory 29608.00k

Timestamp 10/08/2018 01:06:03 PM

Step Count 72 Switch Count 4

Page Faults 0

Page Reclaims 651

Page Swaps 0

Voluntary Context Switches 20

Involuntary Context Switches 0

Block Input Operations 0

Block Output Operations 264

78 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK;

Re: SAS Assignment

Kurt_Bremser — Mon, 08 Oct 2018 14:06:15 GMT

So I saved the Excel to a csv file and copied that to my SAS server. It's much easier to get the right types and contents if you don't force SAS to make guesses (what you have to do with the xlsx format). On top of that, I don't have ACCESS to PC Files licensed.

Excel used a semicolon as delimiter (although csv explicitly means comma seperated volume), so I had to adapt my input data step.

data in (keep=sex category ny kc);
infile
  '$HOME/sascommunity/nyfile.csv' /* filename on my server */
  dlm=';' /* use semicolon as delimiter */
  dsd /* in case a delimiter or an empty cell appears somewhere */
  firstobs=4 /* start in line 4, everything before that is irrelevant */
;
length /* set lengths for the variables needed */
  sex $6
  category $50
  ny 8
  var3 $10 /* var3 is just a dummy, as we only need the population estimates */
  kc 8
;
format ny kc comma12.; /* Nice format for the numbers */
label /* Nice names for the csv file */
  sex = 'Sex'
  category = 'Category'
  ny = 'Pop. Estimate New York'
  kc = 'Pop. Estimate Kings County'
;
retain sex '' flag 0; /* keep these variables across observations */
input
  category
  ny : comma12.
  var3
  kc : comma12.
;
/* determine the sex: */
if category in ('Male:','Female:') then sex = scan(category,1,':');
/* check the category fulltime or not: */
if substr(category,1,6) = 'Worked' then flag = 1;
if substr(category,1,5) = 'Other' then flag = 0;
if flag and category in ( /* only if we are in the right category and sub-category */
  '$55,000 to $64,999',
  '$65,000 to $74,999',
  '$75,000 to $99,999',
  '$100,000 or more'
); /* this is called a subsetting if */
run;

/* check the dataset */
proc print data=in noobs;
run;

/* the easiest part: create the csv */
proc export data=in file='$HOME/sascommunity/want.csv' dbms=csv replace label;
run;

Output of the proc print:

 sex           category                   ny              kc

Male      $55,000 to $64,999         244,273          16,330
Male      $65,000 to $74,999         199,338          14,708
Male      $75,000 to $99,999         311,572          25,495
Male      $100,000 or more           462,618          59,399
Female    $55,000 to $64,999         176,444          13,596
Female    $65,000 to $74,999         130,768          11,639
Female    $75,000 to $99,999         184,576          18,605
Female    $100,000 or more           180,911          29,356

The csv file:

"Sex","Category","Pop. Estimate New York","Pop. Estimate Kings County"
Male,"$55,000 to $64,999","244,273","16,330"
Male,"$65,000 to $74,999","199,338","14,708"
Male,"$75,000 to $99,999","311,572","25,495"
Male,"$100,000 or more","462,618","59,399"
Female,"$55,000 to $64,999","176,444","13,596"
Female,"$65,000 to $74,999","130,768","11,639"
Female,"$75,000 to $99,999","184,576","18,605"
Female,"$100,000 or more","180,911","29,356"

Re: SAS Assignment

jk101564 — Mon, 08 Oct 2018 19:54:16 GMT

thanks for quick reply. I tried that but only title's are showing up in the output.

Re: SAS Assignment

jk101564 — Mon, 08 Oct 2018 17:56:59 GMT

I looked at the logs closer and it is not reading the number.

NOTE: Invalid data for ny in line 5 1-102.
 NOTE: Invalid data for kc in line 7 1-63.
 RULE:     ----+----1----+----2----+----3----+----4----+----5----+----6----+----7----+----8----+----9----+----0                      
 7               With income:,"2,488,381","+/-15,432","180,569","+/-3,300" 63
 NOTE: Invalid data errors for file ''/home/myfolder/nyfile.csv'' occurred outside the printed range.
 NOTE: Increase available buffer lines with the INFILE n= option.
 sex=  category=Male:,"5,148,815","+/-3,463","326,951",+/-302 ny=. var3=No income, kc=. flag=0 _ERROR_=1 _N_=1
 NOTE: Invalid data for ny in line 9 1-52.
 NOTE: Invalid data for kc in line 11 1-57.

Does anyone know how to format those numbers?

Re: SAS Assignment

ChrisHemedinger — Mon, 08 Oct 2018 18:16:10 GMT

Hi @Kurt_Bremser! Looks like you've helped to solve this problem before -- maybe just the County name has changed 😉

Re: SAS Assignment

jk101564 — Mon, 08 Oct 2018 18:26:08 GMT

thank you so much @KurtBremser. I think i got it what was wrong with the code.