About DangIT

DangIT · ‎03-09-2015

Thank you. I like this idea, I will give it a shot. Maybe a dumb question, but would this solution be able to consider the addition of new ID's on a monthly basis without duplication? I'm new to this, so thinking out loud I am imagining a process that will look like: 1. Setup up first initial hash lookup table on existing dataset a. output of a hashed-value pair 2. On a monthly basis I receive a data feed 3. Append the data to my master table 3. Run the hashing program a.only new id's will be generated into the hash table 4. Rollup my analytic dataset by numeric hash ids does that make sense?

DangIT · ‎03-09-2015

Hi ballardw, Yes, mainly because it is much easier to handle. One of the encrypted variables refers to a 'family' and with the concatenation of 2 other variables, I am able to identify a 'member' within the family. For analytics, I will have 2 levels for analysis, the 'Family' and the 'Member'. The Member_ID is what i'm looking to convert to a numeric ID if possible..

DangIT · ‎03-09-2015

Hi, I have received a file with PII variables encrypted to random string of 24 characters. To identify a unique person on my data file I have to concatenate a number of these variables together. The outcome is an ID variable with ~100 character string. Processing this ID is inefficient so I am wondering if there is a way to convert the string ID to a numeric ID and being able to convert it back to the string if I need to? I may have millions of ID's on my data set and new ones coming in every month. Thoughts on the approach is greatly appreciated. Thanks in advance.

DangIT · ‎02-12-2015

Thank you.

DangIT · ‎02-12-2015

Hi, I have a data set with that looks like: ID File_period 1 201301 1 201302 1 201303 2 201302 2 201303 3 201301 I would like a unique data set by ID and a new variable for all the file periods, for example: ID File_Period_1 File_Period_2 File_Period_3 1 201301 201302 201303 2 201302 201301 3 201301 The number of file periods will vary by ID. I'm not sure what the best way to run this loop, any help greatly appreciated. Thanks in advance!

DangIT · ‎12-24-2014

Interesting.. I can probably come up with a business rule, my data will have 15 variables, and the 10th variable is the field that may have the additional comma. I'm lost at how I would insert quotes around the 10th field in a datastep? Thanks, KD

DangIT · ‎12-24-2014

I figured that was the case. Thanks for the quick reply.

DangIT · ‎12-24-2014

Hi, I have received a CSV file with non quoted strings. One of the string variables in the file may contain values with a comma. So when I load the data through Proc Import, those records will all be shifted to an additional variable. Unfortunately the data is not aligned correctly for me to run it with fixed widths. Has anyone figured out a way to get around this? Thanks in advance for your help.

DangIT · ‎11-17-2014

Thank you for pointing me to the YYMMDDxw. format, i've altered the text conversion code to: Date_txt=put(Date, YYMMDDd10.) ;

DangIT · ‎11-17-2014

The issue is that I have tables already in SQL server with the Date (no time) format, reading from SQL server to SAS, isn't a problem, but when I write back I have to convert it to a Datetime format, or if i'm appending/replacing a current table, I will get the data type inconsistency error. The work around I used was to convert the Date to a string and write to the SQL server table with the Date format. In SQL server, the data type for the Date variable is still a Date type and not a string. It seems to write it in as a string from SAS to SQL, but still functions as a Date type when I run queries off of the table.

DangIT · ‎11-17-2014

I found a solution that worked in getting around the datetime conversion, its not an elegant solution, but it worked in my case.. I first created an empty table in SQL-sever with the Date variable as a Date format and used a Proc Append from SAS to SQL. Create TABLE [temp]( [Date] Date , [Sales] Real ); GO I had to change the Date variable from SAS had to be converted to a text variable with the format 'YYYY-MM-DD'. Date_txt=put(Year(Date),4.) || "-" || put(Month(Date),z2.) || "-" || put(Day(Date),z2.) ;

DangIT · ‎11-17-2014

Is that true? I have tables in SQL Server with just a Date format (no time) and I was able to use a BULK INSERT from a text file to upload the data as a Date format. For example, I would create the table as: Create TABLE [temp]( [Date] Date , [Sales] Real ); GO and use BULK INSERT to upload the text file where the Date variable has no time element. Does SQL Server automatically convert the Date values to datetime and then drop the time element?

DangIT · ‎11-14-2014

Hi, I am trying to write a dataset to SQL server from SAS, but the data has a Date variable, that doesn't convert correctly. I am simply using a Libname reference to the SQL server and running a Data step to output into SQL. For example. Data SQL.Table_name ; Set WORK.Table_Name ; run ; When I check the table on the SQL server, the date variable is converted to a datetime variable and obviously the values are incorrect. How can I correctly write a table to SQL server inorder to deal with Date variables? Thanks in advance for your help.

DangIT · ‎10-17-2014

Sorry ignore this thread.. I feel like such an idiot! Fruit_Status=cat(Fruit," ",Status) ; does exactly what i want..

DangIT · ‎10-17-2014

Hi, I am stuck on creating a macro to loop through the values of 2 variables to recode into a new variable. The logic looks like: Variable 'Fruit' has values of 'Banana' 'Apple' 'Orange' Variable 'Status' has values of 'Rotten' 'Fresh' I would like to create the variable 'Fruit_Status' that is a concatenation of Fruit and Status, with values: Banana Rotten Banana Fresh Apple Rotten Apple Fresh etc. The basic logic where I can write out all combinations is: IF Fruit="Banana' and Status='Rotten' THEN Fruit_Status=cat(Fruit," ",Status) ; ... I imagine there is a much more elegant solution. Thank you for your help!

Online Status	Offline
Date Last Visited	‎04-22-2016 02:10 PM

Re: Hash table calculations?

Hash table calculations?

Re: Help with lookups - Proc Sql or Hash?

Help with lookups - Proc Sql or Hash?

Concatenation of values in an array?

Re: Randomization algorithm

Re: Randomization algorithm

Re: Randomization algorithm

Randomization algorithm

Re: SAS date parameter in pass through SQL statement

Re: Incorporating Data merge into macro

Searching a string for a list of words

Re: SAS date parameter in pass through SQL statement

Re: Encrypting - decrypting values?

Re: Encrypting - decrypting values?

Encrypting - decrypting values?

Re: Loop in Data step help

Loop in Data step help

Re: Loading CSV files with Non Quoted Strings

Re: Loading CSV files with Non Quoted Strings

Loading CSV files with Non Quoted Strings

Re: Uploading data to SQL server - issue with Date variable

Re: Uploading data to SQL server - issue with Date variable

Re: Uploading data to SQL server - issue with Date variable

Re: Uploading data to SQL server - issue with Date variable

Uploading data to SQL server - issue with Date variable

Re: Macro to recode multiple variables

Macro to recode multiple variables