Anyone good at SAS?

[Avalon]

I've got an input file that's supposed to come in once per month, and I've been working on some SAS code that would work on this file and output something meaningful. It's a comma delimited input file that looks like this...

user_id=1234,user=joe,page=home,company=AMD
user_id=2345,user=adam,page=support,company=INTEL

If the file were actually this simple I'd just create four fixed variables and read in user_id, user, page, and company, but unfortunately, it's not. There are going to be roughly 100-150 variables, and the bad part is that month to month, this number may change. For instance, this month I get in that file and each line contains 120 variables. Next month, the file might contain 125 variables, and in October, contain 115 variables.

Do you guys have any thoughts on a way I could write the input code to smartly catch all variables every month without having to re-hardcode the program every month?

 

[EvilManagedCare]

OT @ OP: I don't know a thing about SAS, but I checked out your blog. Having been underwhelmed with Sierra Nevada's Pale Ale, I will due to your review, try their extra IPA.

You're doing God's work on your beer quest.

 

[Avalon]

Lol, thanks. I've actually had to put it on a bit of a hiatus due to paying for a wedding, new car, and honeymoon all within 6 months, but I should be starting it back up soon, or at least, easing back into it soon 😛

 

[Leros]

You're gonna write a simple function to parse the string.

1) Look for the first equal sign. Get all the characters before it. This is your key.
2) Skip the equal sign, look for the first comma. Get all the characters before it. This is your value.
3) Skip the comma.
4) If there are still more characters on the line, go to step 1.

 

[sourceninja]

explode on comma into an array

for each element in that array explode the variable into a key and value (explode on =)

then write them into a hash table as key value pairs.

Now you have an array (hash whatever) with a key (think variable name) and a value

in php terms you get

$myarray['name'] = 'Frank';
$myarray['site'] = 'Support';

etc. make an array of these arrays.

Should be just two quick loops. I'm sure there are better ways to do it if I thought about it. That was just a 2 second idea. You could easily do this with an object oriented approach as well and make an array of objects with a property for each 'key'.

 

[Avalon]

You're gonna write a simple function to parse the string.

1) Look for the first equal sign. Get all the characters before it. This is your key.
2) Skip the equal sign, look for the first comma. Get all the characters before it. This is your value.
3) Skip the comma.
4) If there are still more characters on the line, go to step 1.

Yeah, that's where we started off. Had something like this...

Stringpair : $150.;
tempName = SUBSTR(Stringpair,0,FINDC('='));
tempValu = SUBSTR(Stringpair,FINDC('=')+1,LENGTH(Stringpair));
Call Symput(temp, tempName);
&temp = tempValu;

Wasn't liking calling the macro variable within the dataset 😛
May have to go to a hash table as sourceninja suggested. Thanks!

 

[Saint Nick]

Avalon why is your input data changing? If you have something that uniquely identifies each record type (such as a header record, trailer, line item, etc.)

I use SAS for a lot of EDI stuff so let me know. You might have already found your solution by now.

 

[Fallen Kell]

Perl is your friend:

#!/usr/bin/perl

use Getopt::Std;
getopt(f);

my $inputfile = $opt_f;
my %data;

open(IN, "$inputfile") or die("Could not open file: $inputfile  ERROR: $!");

while(<IN>) {
    my @l_data = split(/\,/, $_);
    my %tmp;
    my $user = "";
    foreach my $item (@l_data) {
       my ($key, $value) = ($item =~ /([^=]+)=(.*)/);
       $tmp{$key} = $value;
       if ($key eq "user") {
          $user = $value;
       }
    if ($user neq "") {
       $data{$user} = \%tmp;
    }
}

# all data is now stored in a hash of hash pointers
# with the main key being the user
#
# The following prints everything
foreach my $k (sort(keys(%data))) {
   print "User: $k\n";
   foreach my $subk (sort(keys(%{$data{$k}}))) {
       print "\tProperty: $subk = $data{$k}->{$subk}\n";
    }
}

Or something like that....