cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
gsouther
‎10-25-2016
  • 4 Posts
  • 4 Likes Given
  • 1 Solutions
  • 0 Likes Received

Please help me understand this PROC IMPORT issue - csv

by Fluorite | Level 6 in SAS Programming
‎09-15-2016 10:10 AM
‎09-15-2016 10:10 AM
I have been working on using proc import rather than using the gui and ran across a strange (at least to me) issue. I was provided a csv file which was exported from Active Directory and only consisted of the distinguishedname field, which has commas within the field. It looks like this sample:   "distinguishedname" "CN=servername,OU=Domain Controllers,DC=DC1234,DC=info,DC=com" "CN=servername1,OU=Standard Servers,DC=DC1334,DC=info,DC=com" "CN=servername2,OU=IIS,OU=Standard Servers,DC=DC1434,DC=info,DC=com" "CN=servername3,OU=IIS,OU=Standard Servers,DC=DC1534,DC=info,DC=com" "CN=servername4,OU=Standard Servers,DC=DC1634,DC=info,DC=com" "CN=servername5,OU=Standard Servers,DC=DC1734,DC=info,DC=com" "CN=servername6,OU=IIS,OU=Standard Servers,DC=DC1834,DC=info,DC=com" "CN=servername7,OU=IIS,OU=Standard Servers,DC=DC1934,DC=info,DC=com" using this proc import statement:     proc import datafile='myfile.csv' dbms=csv out=work.procimportexcel replace; getnames=yes; run; I get this output:     distinguishedname "CN=servername "CN=servername1 "CN=servername2 "CN=servername3 "CN=servername4 "CN=servername5 "CN=servername6 "CN=servername7   I thought it had something to do with the length of the variable name, but it I tried various things and could not get it to include the full field. it always cut it off at the first comma.   When I tried importing a very similar file, the only difference being additional columns, everything worked fine. So i added a column to my original file and just put some bogus data in it:   distinguishedname,test1 "CN=servername,OU=Domain Controllers,DC=DC1234,DC=info,DC=com",345 "CN=servername1,OU=Standard Servers,DC=DC1334,DC=info,DC=com",346 "CN=servername2,OU=IIS,OU=Standard Servers,DC=DC1434,DC=info,DC=com",348 "CN=servername3,OU=IIS,OU=Standard Servers,DC=DC1534,DC=info,DC=com",349 "CN=servername4,OU=Standard Servers,DC=DC1634,DC=info,DC=com",350 "CN=servername5,OU=Standard Servers,DC=DC1734,DC=info,DC=com",351 "CN=servername6,OU=IIS,OU=Standard Servers,DC=DC1834,DC=info,DC=com",352 "CN=servername7,OU=IIS,OU=Standard Servers,DC=DC1934,DC=info,DC=com",353 This file imports fine too without changing the proc import code at all. I get the full value of the first column, with no leading quotes as well as the second column.     Can someone help me understand why the file with only one column is not importing correctly? It seems that when there is only one field and it contains commas within the quotes, SAS doesn't understand to put it all in one field and wants to use the commas as delimiters. When there is more than one field it seems to know to ignore the commas within quotes and treat the commas outside of the quotes as delimiters. Thanks. ... View more

Re: How to create dynamic suffix on a numbered variable list?

by Fluorite | Level 6 in SAS Programming
‎08-31-2016 10:02 AM
‎08-31-2016 10:02 AM
Thanks for the help! I have it working now with the code below.   proc sql; select max(count(distinguishedname,","))+1 into : commacount from have; quit; %let commacount = &commacount; data something; set have; array parsed_vars{&commacount}$50 VAR1-VAR&commacount; run; ... View more

Re: How to create dynamic suffix on a numbered variable list?

by Fluorite | Level 6 in SAS Programming
‎08-31-2016 08:13 AM
‎08-31-2016 08:13 AM
Thanks for the response! I was missing the into: clause. It seems to be getting a little further, but I am still getting the error about missing the numeric suffix.   Code: proc sql; select max(count(distinguishedname,","))+1 into : commacount from have; quit; data something; set have; array parsed_vars{&commacount}$50 VAR1-VAR&commacount; run;       28 data something; 29 set have; 30 array parsed_vars{&commacount}$50 VAR1-VAR&commacount; NOTE: Line generated by the macro variable "COMMACOUNT". 30 VAR 9 _ 22 200 ERROR: Missing numeric suffix on a numbered variable list (VAR1-VAR). ERROR: Too few variables defined for the dimension(s) specified for the array parsed_vars. ERROR 22-322: Syntax error, expecting one of the following: a name, (, ;, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_. ERROR 200-322: The symbol is not recognized and will be ignored. ... View more

How to create dynamic suffix on a numbered variable list?

by Fluorite | Level 6 in SAS Programming
‎08-31-2016 07:46 AM
‎08-31-2016 07:46 AM
Hello, I am just learning SAS and so far have not found a way to dynamically create variables based on a count of a character in a string. I am parsing out a string from Active Directory (for example: CN=N123456,OU=Users,OU=Admins,DC=D01,DC=com). I have successfully created collumns for each item following a comma, but it is not dynamic. I have been running a proc sql query to find the Active Directory string with the most commas, and then creating an array based on that number.   In the file I am currently working with, the largest number of commas is 7, so I need 8 columns. I have been creating varialbes like this:   array parsed_vars(8)$50 VAR1-VAR8; I want to store the number of collumns I need based on my comma count and pass that into the array, something like this:   array parsed_vars(nmbrvar)$50 VAR1-VAR||nmbrvar; The log shows this: ERROR: Missing numeric suffix on a numbered variable list (OU1-OU).   Is there a way to do this? I am working in SAS Enterprise Guide 5.1. Thanks. ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎10-25-2016 01:33 PM
Likes given to
View All
Quick links