thanks for the help! ... View more

Hi rykwong,   This topic has recently come up here on the SAS Support Communities, see this thread. This isn't a site for job postings. Best to try LinkedIn and other job sites.   BTW, your headline to this post doesn't really match the subject. Maybe that was a mistake, or an article you were just looking at (A quick guide to connecting to Oracle from SAS). I'm going to edit the headline to better represent the subject.   Best, Anna ... View more

@rykwong wrote:   the data is stored in a flat csv sheet.  the actual data is not colored (please see attached screenshot).  I just colored the text that I want for the sake of explaining what I need to extract.   would you have a sas code that shows me how to use proc sql to extract the needed values?     You're not showing an example of a CSV file. You are showing pictures of (most likely) Excel interpretations of a csv. Since the presence or non-presence of line feed or similar characters is important then perhaps you coult attach, as TEXT file format not PNG, a few example rows. Then we would have something we could actually test code against. ... View more

If you are using DBMS=XLSX then I don't think you can (or need) to use GUESSINGROWS.  With that engine SAS will read the XLSX file directly and figure out what is in the columns.   If you have trouble when you remove it please explain what trouble it is causing? ... View more

Although Karlb's solution will work, it tends to be slow. An alternative solution is to transform the data from wide form to long form. The new form of the data will contain 600 BY groups. The first contains the data for Y, Drug, and X1.  The second contains the data for Y, Drug, and X2. And so on.   For an example and discussion, see the article "An easy way to run thousands of regressions in SAS" ... View more

thanks so much.  this solves this issue.  Much appreciated! ... View more

Run this example and study how it works.   proc transpose data=sashelp.class out=class; by name age sex; var height weight; run; proc sort data=class; by _name_; run; proc print; run; proc glm; by _name_; class sex; model age= sex sex*col1; run; ... View more

Many users here don't want to download Excel files because of virus potential, others have such things blocked by security software. Also if you give us Excel we have to create a SAS data set and due to the non-existent constraints on Excel data cells the result we end up with may not have variables of the same type (numeric or character) and even values.   https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 has instructions on turning SAS data sets into data step code we can run to duplicate your data set.   Since your image post shows a p-value then you are apparently doing some test. What test would that be? Do you already have all those values and are just trying to show the table or do you need to generate the statistics and then show a table? If the later then you need to provide a much more detailed description of your existing data and what you are attempting to portray. ... View more

Excel is not a good data transfer tool, you will find many problems with it.  You have hit one of them.  Excel can have a lot of properties/hidden things on cells.  What is likely to have happened is someone has applied a format, or styling to all columns, therefore Excel assumes the working range is all columns.  Thus when SAS imports the file, it checks the working area and imports all columns specified in the working range.  To get round it, select the column aftet the last column of data, then select all rows to the right of that as well (CNTRL + Shift + Right arrow), then right click on that and select delete.   Alternatively, and far better (more robust, easy to re-produce, safer etc.) is to get the file in a proper data transfer file format, delimited file, XML etc.  If you got a CSV, you would then write a datastep program which specified each variable to be read in, how its to be read in, what format/length, any other processing.  Far more control and accuracy than some dodgy Excel file being read in by a guessing procedure. ... View more

Excel is not a good data transfer tool, you will find many problems with it.  You have hit one of them.  Excel can have a lot of properties/hidden things on cells.  What is likely to have happened is someone has applied a format, or styling to all columns, therefore Excel assumes the working range is all columns.  Thus when SAS imports the file, it checks the working area and imports all columns specified in the working range.  To get round it, select the column aftet the last column of data, then select all rows to the right of that as well (CNTRL + Shift + Right arrow), then right click on that and select delete.   Alternatively, and far better (more robust, easy to re-produce, safer etc.) is to get the file in a proper data transfer file format, delimited file, XML etc.  If you got a CSV, you would then write a datastep program which specified each variable to be read in, how its to be read in, what format/length, any other processing.  Far more control and accuracy than some dodgy Excel file being read in by a guessing procedure. ... View more

many thanks for your help!   I can manually clean the excel sheets to remove the columns (on the right of the sheet) that do not contain any data, but I was hoping that there may be a sas code that can auto delete these columns on the right.  The reason is each time I received this sheet(s) the empty columns on the right may be under different labels and it is painstaking to manually delete these columns each time.       ... View more

many thanks for your help!   I can manually clean the excel sheets to remove the columns (on the right of the sheet) that do not contain any data, but I was hoping that there may be a sas code that can auto delete these columns on the right.  The reason is each time I received this sheet(s) the empty columns on the right may be under different labels and it is painstaking to manually delete these columns each time.       ... View more

Hi Raymond,    It's likely SAS isn't reading the correct type for the variable.  You can try setting GUESSINGROWS=32000 or the number of records in your file so that it scans the full table before assigning a variable type.   Another option - which I generally recommend - is to look at your log after the PROC IMPORT. The Log has the import code, but in a data step format. If you copy that code, holding down CTRL+ALT while selecting to avoid line numbers, you can customize it to your needs. Specifically, check the informat/format and input statement for the variables that are causing errors in your log.   It looks like you have four specific variables causing the errors:   NOTE: Invalid data for id in line 689 1-29. NOTE: Invalid data for lvedvi in line 689 36-55. NOTE: Invalid data for lvesvi in line 689 58-62. NOTE: Invalid data for rvedvi in line 689 163-178. ... View more