SAS Macro: How to Retrieve a Value from a Dataset

December 24, 2009 by
Filed under: Uncategorized 

If you're new here, you may want to subscribe to my RSS feed. Thanks for visiting!

SAS is a very powerful language when one have to deal with processing huge datasets. It becomes much more complicated when one wants to play with single elements. I recently had the need to put a specific value of a dataset into a macro variable. With the help of the SAS support team of Switzerland, I have made a small macro for that. Here is the definition of the function:

  • Macro name: Get_data
  • Input: myDataset (name of the dataset), myLine (line number), myColumn (name of the column), myMVar (name of the macro variable used to store the result)
  • Output: A new macro variable named &myMVar.

And here is the corresponding code:

%MACRO Get_data(myDataset=,myLine=,myColumn=,myMVar=);
%GLOBAL &myMVar.;
data _null_;
set &myDataset.;
if _N_ = &myLine. then do;
call symput(symget('myMVar'),&myColumn.);
end;
run;
%MEND Get_data;

Of course, I would be very interested if someone found a simplier way of performing this action (and I think SAS support would be interested as well).

Share

Comments

31 Comments on SAS Macro: How to Retrieve a Value from a Dataset

  1. lordthias on Thu, 24th Dec 2009 2:54 pm
  2. Hi Sandro,
    Here’s an other way to do the same think (maybe a little faster on big dataset)

    %MACRO Get_data(myDataset=,myLine=,myColumn=,myMVar=);
    %GLOBAL &myMVar;
    proc sql noprint;
    select &myColumn into :&myMVar
    from &myDataset
    where monotonic() = &myLine;
    quit;
    %MEND Get_data;

    %Get_data(myDataset=tablename,myLine=linenumber,myColumn=colname,myMVar=varname)
    %put &varname;

    I let you test the execution time ;)

    Joyeuse fête de noël !

  3. Sandro Saitta on Thu, 24th Dec 2009 4:05 pm
  4. @lordthias: Thanks for the PROC SQL example. I’m not using the get_data macro on big data sets for the moment, but I would of course compare both codes before using it on huge files :-) A toi aussi et bonne année!

  5. Chris Hemedinger on Thu, 24th Dec 2009 4:16 pm
  6. I second the suggestion for using PROC SQL SELECT INTO. The concept of sequential records holds true for SAS datasets, but if your data is in a DBMS, you might want a different mechanism for pulling out a specific record (value in an ID column, for example).

  7. Sandro Saitta on Sat, 26th Dec 2009 7:35 pm
  8. @Chris: Thanks for sharing your opinion. Also PROC SQL is closer to mySQL and may be worth considering if planning to move from SAS to another ETL tool (Talend, etc.)

  9. Shane on Wed, 30th Dec 2009 9:59 am
  10. Another benefit of PROC SQL is it doesn’t require sorted datasets for a join… where as DATA …; MERGE … BY … will throw an error if it is not sorted — very annoying!!

  11. Sandro Saitta on Sun, 3rd Jan 2010 1:23 pm
  12. @Shane: Good point! This is something we should keep in mind when comparing DATA step with PROC SQL execution time.

  13. Manish on Thu, 14th Jan 2010 12:01 pm
  14. Hi

    This is the simplest and the most efficient code I can think of (would be hapy to learn better way to do this).

    —————————————————————-

    %MACRO Get_data(myDataset=,myLine=,myColumn=);
    %GLOBAL myMVar;
    data _null_;
    pull = &myLine; /*assigning value of macto var to datastep*/
    set &myDataset (keep=&myColumn) point=Pull;
    call symput(‘myMVar’,&myColumn);
    output; stop;
    run;
    %MEND Get_data;

    %Get_data(myDataset=,myLine=,myColumn=);
    %put &myMVar; /*to print value of macro var in log*/

    —————————————————————-
    It is effiencie because it will read only the line that you want SAS to read. If ‘If – Then’ is used, sas reads each obs and then does the evalauation of IF condition, more input-output. This can be optimised by telling sas to read only the required row using ‘Point=’ option. But ‘Point=’ option takes only variable name. Hence, before set statement, a variable called ‘Pull’ is defined with the value of required row-number.. Now sas just reads the required row saving i/o. DO NOTE that ‘Output;’ and ‘Stop’ must be used with ‘Point=’ option of you will execute a never-ending data-step;

    I hae further optimised the program by using ‘KEEP=’ optin in set statement, which asks SAS to read only relevant columns.

    The syntax of ‘Call Sumput’ is actually simpler than what you have used here. You don’t need to use Symget here.

    Hope this helps.

    Manish

  15. Sandro Saitta on Thu, 14th Jan 2010 12:47 pm
  16. @Manish: Thanks for your example Manish. I haven’t tested it yet, but it seems (really) optimized by only reading the correct row/column. Thanks for your input!

  17. Song Liu on Thu, 18th Feb 2010 9:03 pm
  18. Use sas I/O funtions would be effient. Here is the code:
    %MACRO Get_data(myDataset,myLine,myColumn);
    %global myMvar;
    %let dsid=%sysfunc(open(&myDataset.,i));
    %let rc=%sysfunc(fetchobs(&dsid, &myLine.));
    %if &rc = 0 %then %let myMvar=%sysfunc(GETVARN(&dsid,%sysfunc(varnum(&dsid,&myColumn)))); /****use getvarc for character variables*****/
    %let rc=%sysfunc(close(&dsid));
    %MEND Get_data;

  19. RolandRB on Tue, 1st Feb 2011 3:55 am
  20. I have a macro called %getvalue to do this that uses SAS I/O functions as above. The advantage with this method is that you can have a function-style maco to do this that allows you to call it in the middle of a data step if need be.

    http://www.datasavantconsulting.com/roland/Spectre/utilmacros/getvalue.sas

    More macros here:
    http://www.datasavantconsulting.com/roland/Spectre/maclist2.html

  21. Sandro Saitta on Thu, 3rd Feb 2011 8:23 pm
  22. @RolandRB: Thanks for the links!

  23. fab on Sun, 8th Apr 2012 11:27 am
  24. I look for something like ‘setvalue’
    is it possible to write a value of a macro variable to a certain observation in a data set?
    I want to navigate with the line number as well.
    data and point with symget?

  25. Android Training in Chennai on Mon, 9th Feb 2015 12:56 pm
  26. Hi this is Kathiresan i am having 3 years of experience as a dot net developer and i am certified. i have knowledge on OOPS concepts in .NET but dont know indepth. After learning android will be enough to get a good career in IT with good package? and i crossed Android Training in Chennai website where someone please help me to identity the syllabus covers everything or not??

    Thanks,
    kathiresan

  27. Android Training in Chennai on Mon, 9th Feb 2015 1:33 pm
  28. Hi this is Kathiresan i am having 3 years of experience as a dot net developer and i am certified. i have knowledge on OOPS concepts in .NET but dont know indepth. After learning android will be enough to get a good career in IT with good package? and i crossed Android Training in Chennai website where someone please help me to identity the syllabus covers everything or not??

    Thanks,
    kathiresan

  29. Oracle Training on Wed, 18th Feb 2015 8:30 am
  30. Your blog is really useful for me. Thanks for sharing this informative blog. If anyone wants to get real time Oracle Training in Chennai reach FITA located at Chennai. They give professional and job oriented training for all students.

  31. SAP Training on Sat, 28th Feb 2015 12:26 pm
  32. Thanks for sharing this informative blog. FITA provides SAP Training in Chennai with years of experienced professionals and fully hands-on classes. SAP is one of the CRM. Today’s most of the IT industry use this software for customer relationship management. To know more details about sap reach FITA Academy. Rated as No.1 SAP Institutes in Chennai.

  33. Oracle Training in Chennai on Mon, 2nd Mar 2015 1:55 pm
  34. Your blog is really useful for me. Thanks for sharing this informative blog. If anyone wants to get real time Oracle Training in Chennai reach FITA Oracle Training Institutes in Chennai. They give professional and job oriented training for all students.

  35. Salesforce Training on Fri, 6th Mar 2015 10:52 am
  36. Thanks for sharing this informative blog. FITA provides Salesforce Training in Chennai with years of experienced professionals and fully hands-on classes. Salesforce is a cloud based CRM software. Today’s most of the IT industry use this software for customer relationship management. To know more details about salesforce reach FITA Academy. Rated as No.1 Salesforce Training Institutes in Chennai.

  37. Digital Marketing Training on Wed, 11th Mar 2015 7:41 am
  38. Thanks for sharing this informative blog. Recently I did Digital Marketing Training in Chennai at a leading digital marketing company. It’s really useful for me to make a bright career. To know more details about this course please visit FITA.

  39. Android Training Institutes in Chennai on Sat, 14th Mar 2015 7:20 am
  40. Your blog is really useful for me. Thanks for sharing this useful blog..Suppose if anyone interested to learn Android Training in Chennai please visit fita academy which offers best Android Course in Chennai at reasonable cost.

  41. Android Training in Chennai on Wed, 18th Mar 2015 7:53 am
  42. Thanks for your informative article. Android SDK allows you to create stunning mobile application loaded with more features and enhanced priority. With basis on Java coding language, you can create stunning mobile application with ease. Best Android Training institute in Chennai

  43. Android Course in in Chennai on Wed, 18th Mar 2015 7:55 am
  44. Thanks for your informative article. Android SDK allows you to create stunning mobile application loaded with more features and enhanced priority. With basis on Java coding language, you can create stunning mobile application with ease.

  45. Ios Training in Chennai on Thu, 19th Mar 2015 9:09 am
  46. Excellent post on iOS mobile apps development!!! The future of mobile application development is on positive note. You can make most it by having in-depth knowledge on mobile application development platform and other stunning features.

  47. SEO Training on Wed, 25th Mar 2015 6:51 am
  48. Your information is really useful for me.Thanks for sharing such a valuable information. If anyone wants to get SEO Training in Chennai visit FITA Academy located at Chennai. Rated as No.1 SEO Training institute in Chennai.

  49. Jenny on Tue, 7th Apr 2015 8:41 am
  50. dataset is really helpful. and can retrive a value from the dataset.Loadrunner Training in Chennai | Software Testing Training in Chennai | QTP Training in Chennai

  51. Java training in Chennai on Mon, 13th Apr 2015 12:51 pm
  52. Bind blowing. You have achieved your target. People should follow your blog for getting more information and their basic needs.

    Java training in Chennai | Android training in chennai | Java training in Chennai

  53. Android Training on Tue, 21st Apr 2015 8:36 am
  54. I agree with your post. Android software development kit makes the application development process lot simpler and effective. You can create best performing android application with ease. Android Training Institutes in Chennai | Android Training in Chennai

  55. jercy on Tue, 21st Apr 2015 10:08 am
  56. SAS is a type of Business Analytics Software.In these several topics are involved.Retriving the values in the dataset is the important one.These blog explained very clearely about the topic.It really nice.
    Loadrunner Training in Chennai | Loadrunner Training in Chennai | Loadrunner Training in Chennai

  57. Android Training on Tue, 21st Apr 2015 12:21 pm
  58. Hi, I am Emi lives in Chennai. I am technology freak. I did Android mobile application development course in Chennai at reputed training institutes, this is very usful for me to make a bright carrer in IT industry. So If you looking for best Android Training Institutes in Chennai please visit fita academy. Android Training in Chennai

  59. dhanalakshmi on Fri, 24th Apr 2015 10:17 am
  60. Nice article i was really impressed by seeing this article, it was very interesting and it is very useful for me.. I get a lot of great information from this blog. Thank you for your sharing this informative blog. Android Training in chennai | Android Training chennai | Android course in chennai | Android course chennai

  61. John on Thu, 7th May 2015 1:03 pm
  62. PROC CONTENTS would be the quickest way to get that information in a dataset. Column names can be found in the column NAME.proc contents data=sashelp.class out=contents noprint; run;

    SAP Success factor Training in Chennai

Tell me what you're thinking...





  • Swiss Association for Analytics

  • Most Popular Posts

  • T-shirts, Mugs & Mousepads


    All benefits given to a charity association
  • Data Mining Search Engine

    Supported by AnalyticBridge

  • Archives

  • Reading Recommandations